Steffen Uhlig, November 30, 2016 - 12:01 pm UTC
Attention! - The problem remains also if I use the same alias name!!! - that means - I have to use in every case a different alias!!
November 30, 2016 - 2:07 pm UTC
What do you mean "same alias name"?
Resolution by automation
Duke Ganote, November 30, 2016 - 3:06 pm UTC
I stumbled across this behavior several months ago when I was doing some data profiling. Suddenly I realized I needed to double-check my results! The behavior is easily demonstrated:
SQL> select count(*) from user_tables where table_name in ( select table_name from user_views );
COUNT(*)
------------
58
SQL> select count(*) from user_tables --where table_name in ( select table_name from user_views );
COUNT(*)
------------
58
Clearly there's no TABLE_NAME column in USER_VIEWS, but the first query runs!
I was looking for anomalies between a reference table and the transaction table, and I never found any. I kept plugging away -- until at one point I realized I was comparing a "reference table" that didn't even
have the column in question:
SQL> select count(*) from user_tables where table_name not in ( select table_name from user_views );
COUNT(*)
------------
0
USER_VIEWS contains the names of every table?? No... not quite!
November 30, 2016 - 4:02 pm UTC
Indeed. Another example of the importance of qualifying your columns!
10053 Trace
Rajeshwaran, Jeyabal, December 01, 2016 - 6:56 am UTC
Looking into the 10053 Trace could explain the things even better.
demo@ORA12C> @10053
Session altered.
demo@ORA12C> explain plan for
2 SELECT a.tb_id
3 FROM suh_tb_test a
4 WHERE a.tb_id NOT IN (SELECT tb_id FROM suh_tb_test_1);
Explained.
demo@ORA12C> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "A"."TB_ID" "TB_ID"
FROM "DEMO"."SUH_TB_TEST" "A"
WHERE NOT EXISTS
(SELECT 0
FROM "DEMO"."SUH_TB_TEST_1" "SUH_TB_TEST_1"
WHERE LNNVL("A"."TB_ID"<>"A"."TB_ID")
)
December 01, 2016 - 9:49 am UTC
Reaching for a 10053 trace to "solve" this seems a bit extreme to me...