Smells like oracle bug?

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> create table blahh (id1 number, id2 number);

Table created.

SQL> create index idx1 on blahh (id1);

Index created.

SQL> create index idx2 on blahh (id2);

Index created.

SQL> insert into blahh values (1, null);

1 row created.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(user, 'blahh', cascade=>true)

PL/SQL procedure successfully completed.

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 /

'
-

x

Execution Plan

   0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=2 Card=1)    1 0 FILTER

   2    1     TABLE ACCESS (FULL) OF 'DUAL' (Cost=2 Card=1)
   3    1     TABLE ACCESS (FULL) OF 'BLAHH' (Cost=2 Card=1 Bytes=3)

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 /

no rows selected

Execution Plan

   0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=2 Card=1)    1 0 FILTER

   2    1     TABLE ACCESS (FULL) OF 'DUAL' (Cost=2 Card=1)
   3    1     TABLE ACCESS (BY INDEX ROWID) OF 'BLAHH' (Cost=1 Card=1
          Bytes=3)

   4    3       INDEX (FULL SCAN) OF 'IDX2' (NON-UNIQUE)

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 /

'
-

x

1 row 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     TABLE ACCESS (FULL) OF '<tablename>' (Cost=2 Card=1 By
          tes=14)

Gints Plivna
http://www.gplivna.eu/
--

http://www.freelists.org/webpage/oracle-l Received on Thu Jul 13 2006 - 10:15:21 CDT