Short description - different results for the same selects with or without rather innocent hints. Actually I found it on other tables in our system, but at least I can reproduce a testcase.
Same results was on 9.2.0.4 and 9.2.0.7. Unfortunately haven't other bases handy to check this. Probably someone can check whether I'm unique in this Oracle world or not?
Connected to: Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.7.0 - Production
SQL> set autotrace on SQL> select 'x' from dual where exists ( 2 select 'z' from blahh where 3 (id1 = 1 and id2 is null) or (id1 is null and id2 = 1)) 4 /
SQL> select 'x' from dual where exists ( 2 select /*+ index_ffs (blahh idx1 idx2) */ 'z' from blahh where 3 (id1 = 1 and id2 is null) or (id1 is null and id2 = 1)) 4 /
Trace file also shows the same plans, so autotrace isn't lying this time. Actually in production the plans were different and here they looked as follows:
SQL> select 'x' from dual where exists ( 2 SELECT 'z' 3 FROM <tablename> 4 WHERE (Radp_Rrpr_Id = 10000003254 AND Radp_Rrpr_Id1 IS NULL) OR 5 (Radp_Rrpr_Id IS NULL AND Radp_Rrpr_Id1 = 10000003254)) 6 /
no rows selected
Elapsed: 00:00:00.03
Execution Plan -- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=82) 1 0 FILTER 2 1 TABLE ACCESS (FULL) OF 'DUAL' (Cost=2 Card=82) 3 1 VIEW OF 'index$_join$_002' (Cost=4 Card=1 Bytes=14) 4 3 HASH JOIN 5 4 INDEX (FAST FULL SCAN) OF 'IX_RADP_RRPR_ID' (NON-UNI QUE) (Cost=4 Card=1 Bytes=14)
6 4 INDEX (FAST FULL SCAN) OF 'IX_RADP_RRPR_ID1' (NON-UN IQUE) (Cost=4 Card=1 Bytes=14)
SQL> select 'x' from dual where exists ( 2 SELECT /*+ full (<tablename>) */'z' 3 FROM <tablename> 4 WHERE (Radp_Rrpr_Id = 10000003254 AND Radp_Rrpr_Id1 IS NULL) OR 5 (Radp_Rrpr_Id IS NULL AND Radp_Rrpr_Id1 = 10000003254)) 6 /
