Smells like oracle bug?

  • From: "Gints Plivna" <gints.plivna@xxxxxxxxx>
  • To: oracle-l <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 13 Jul 2006 18:15:21 +0300

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/
--
//www.freelists.org/webpage/oracle-l


Other related posts: