Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Queries have different plan with UNION

User505978 - OracleJun 5 2013 — edited Jun 6 2013
I have 2 query like this:

select * from emp where emp_no=2;

select * from emp where emp_no=3;

when i look at their execution plan seperately, it works fine and no problem but,

when i do this;

select * from emp where emp_no=2
union
select * from emp where emp_no=3;

i see different plan for both.

Is there a way to make use their own plan independently in union statement?

Comments

Gaz in Oz

By not showing all your code, it is difficult to say what you have done wrong. Post a VERY SIMPLE and SHORT complete example that shows what you are trying to do and how you are trying to do it...

The actual Oracle error ORA-00904 suggests that the query sqlalchemy is throwing to the Oracle database contains a non-existent column name, not an issue with a date or timestamp.

Add debug your code and output to screen the actual query sqlalchemy is trying to send to the database.

As a general tip, use sqlplus to test out "stuff". If you don't already have it on your machine, you can download Oracle instantclient and tools from here:

https://www.oracle.com/technetwork/database/database-technologies/instant-client/overview/index.html

With basic or basic-light and sqlplus installed you can run the above sqlalchemy  debug output query in an Oracle client, connected to the database with the same credentials, and see exactly what line and where the error is occurring. Yo can also "describe" the table you are trying to insert into, or select from, or what ever.

Here's a very simple example sqlplus session with a simple SELECT query failing, to give you an idea of how you would start to debug your above error in sqlplus:

$ sqlplus gaz/gaz@host:port/service_name
...
SQL> select col1,
  2         dummy
  3  from   dual;
select col1,
       *
ERROR at line 1:
ORA-00904: "COL1": invalid identifier
SQL> describe dual
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 DUMMY                                  VARCHAR2(1)
SQL> l
  1  select col1,
  2         dummy
  3* from   dual
SQL> 1
  1* select col1,
SQL> c/col1,//
  1* select
SQL> l
  1  select
  2         dummy
  3* from   dual
SQL> /
D
-
X
1 row selected.
SQL>
1 person found this helpful
1 - 1
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jul 4 2013
Added on Jun 5 2013
13 comments
12,050 views