Query not using index

Laurent Schneider · Sep 19, 2008 11:54AM

Hi,
I am wondering if there is a way to make my query use an index without specifying the INDEX hint.

Thanks
Laurent

PS: I am using 9.2.0.8

SQL> create table lsc_t1(a number primary key, b number);

Table created.

SQL> create table lsc_t2(a number primary key, b number);

Table created.

SQL> create table lsc_t3(a number primary key, b number);

Table created.

SQL> insert into lsc_t1(a,b) select rownum, dbms_random.value from dual connect by level<10000;

9999 rows created.

SQL> insert into lsc_t2(a,b) select rownum, dbms_random.value from dual connect by level<1000;

999 rows created.

SQL> insert into lsc_t3(a,b) select rownum, dbms_random.value from dual connect by level<1000;

999 rows created.

SQL> commit;

Commit complete.

SQL> begin
  2    dbms_stats.gather_table_stats(user,'LSC_T1');
  3    dbms_stats.gather_table_stats(user,'LSC_T2');
  4    dbms_stats.gather_table_stats(user,'LSC_T3');
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> set timi on
SQL> select --+ INDEX(lsc_t1)
  2    * from lsc_t1
  3  where a in (select a from lsc_t2 where b<.01 union all select a from lsc_t3 where b<.01);

         A          B
---------- ----------
       140 .138460932
       161 .928661302
       281 .830010122
       440 .071851157
       443 .355640404
       516 .791276412
       598 .508219846
       600 .158621147
       643 .748363911
       747 .777346635
       836 .390349029
       869 .292453677
       874 .914353702
       881 .930301092

14 rows selected.

Elapsed: 00:00:00.03
SQL> select
  2    * from lsc_t1
  3  where a in (select a from lsc_t2 where b<.01 union all select a from lsc_t3 where b<.01);

         A          B
---------- ----------
       140 .138460932
       161 .928661302
       281 .830010122
       440 .071851157
       443 .355640404
       516 .791276412
       598 .508219846
       600 .158621147
       643 .748363911
       747 .777346635
       836 .390349029
       869 .292453677
       874 .914353702
       881 .930301092

14 rows selected.

Elapsed: 00:00:00.26
SQL> set timi off autot trace exp
SQL> select --+ INDEX(lsc_t1)
  2    * from lsc_t1
  3  where a in (select a from lsc_t2 where b<.01 union all select a from lsc_t3 where b<.01);

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=19 Card=17 Bytes=476)
   1    0   NESTED LOOPS (Cost=19 Card=17 Bytes=476)
   2    1     VIEW OF 'VW_NSO_1' (Cost=2 Card=17 Bytes=68)
   3    2       SORT (UNIQUE)
   4    3         UNION-ALL
   5    4           TABLE ACCESS (FULL) OF 'LSC_T2' (Cost=2 Card=9 Bytes=216)
   6    4           TABLE ACCESS (FULL) OF 'LSC_T3' (Cost=2 Card=8 Bytes=192)
   7    1     TABLE ACCESS (BY INDEX ROWID) OF 'LSC_T1' (Cost=1 Card=1 Bytes=24)
   8    7       INDEX (UNIQUE SCAN) OF 'SYS_C001186489' (UNIQUE)



SQL> select
  2    * from lsc_t1
  3  where a in (select a from lsc_t2 where b<.01 union all select a from lsc_t3 where b<.01);

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=17 Bytes=476)
   1    0   NESTED LOOPS (Cost=6 Card=17 Bytes=476)
   2    1     TABLE ACCESS (FULL) OF 'LSC_T1' (Cost=6 Card=9999 Bytes=239976)
   3    1     VIEW OF 'VW_NSO_1'
   4    3       SORT (UNIQUE)
   5    4         UNION-ALL (PARTITION)
   6    5           TABLE ACCESS (BY INDEX ROWID) OF 'LSC_T2' (Cost=2 Card=1 Bytes=24)
   7    6             INDEX (UNIQUE SCAN) OF 'SYS_C001186490' (UNIQUE) (Cost=1 Card=1)
   8    5           TABLE ACCESS (BY INDEX ROWID) OF 'LSC_T3' (Cost=2 Card=1 Bytes=24)
   9    8             INDEX (UNIQUE SCAN) OF 'SYS_C001186491' (UNIQUE) (Cost=1 Card=1)

Randolf Geist · Sep 22, 2008 4:06AM

Laurent,

if you check my post carefully you'll notice that the "exists" rewrite using the the filter condition outside the "union all" generates a plan that is - although not identical due to the "SORT UNIQUE" operation being outside instead of inside the view definition - from an "execution" perspective "equal" from my point of view. Please let me know if you think this is not the case.

Regards,
Randolf

Oracle related stuff blog:
http://oracle-randolf.blogspot.com/

SQLTools++ for Oracle (Open source Oracle GUI for Windows):
http://www.sqltools-plusplus.org:7676/
http://sourceforge.net/projects/sqlt-pp/

Edited by: Randolf Geist on Sep 22, 2008 10:09 AM

Tubby's solution is not equal due to performing the sort after the NESTED LOOP

Tubby · Sep 19, 2008 12:48PM

I assume re-writing the query is ok?

I tested this out on 9.2.0.8.....

with data as

(

   select * 

   from lsc_t2 

      union all 

   select * 

   from lsc_t3 

)

select 

   distinct l.*

from lsc_t1 l, data d

where l.a = d.a 

and   d.b < .01 ;

Karthick2003 · Sep 19, 2008 12:57PM

You have not gathered statistics for your Index on the tables.

You need to use cascade=>true.

SQL> begin
  2    dbms_stats.gather_table_stats(user,'LSC_T1',cascade=>true);
  3    dbms_stats.gather_table_stats(user,'LSC_T2',cascade=>true);
  4    dbms_stats.gather_table_stats(user,'LSC_T3',cascade=>true);
  5  end;
  6  /

Thanks,

Karthick.

Nicolas Gasparotto · Sep 19, 2008 2:31PM

Laurent Schneider wrote:
Hi,
I am wondering if there is a way to make my query use an index without specifying the INDEX hint.

SQL> select
  2  *
  3  from lsc_t1
  4  where a in (select a from lsc_t2 where b<.01 union all select a from lsc_t3 where b<.01);

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=20 Bytes=560)
   1    0   NESTED LOOPS (Cost=6 Card=20 Bytes=560)
   2    1     TABLE ACCESS (FULL) OF 'LSC_T1' (Cost=6 Card=9999 Bytes=239976)
   3    1     VIEW OF 'VW_NSO_1'
   4    3       SORT (UNIQUE)
   5    4         UNION-ALL (PARTITION)
   6    5           TABLE ACCESS (BY INDEX ROWID) OF 'LSC_T2' (Cost=2 Card=1 Bytes=24)
   7    6             INDEX (UNIQUE SCAN) OF 'SYS_C003365' (UNIQUE) (Cost=1 Card=1)
   8    5           TABLE ACCESS (BY INDEX ROWID) OF 'LSC_T3' (Cost=2 Card=1 Bytes=24)
   9    8             INDEX (UNIQUE SCAN) OF 'SYS_C003366' (UNIQUE) (Cost=1 Card=1)

SQL> alter session set optimizer_mode=rule;

Session altered.

SQL> select
  2  *
  3  from lsc_t1
  4  where a in (select a from lsc_t2 where b<.01 union all select a from lsc_t3 where b<.01);

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=RULE
   1    0   NESTED LOOPS
   2    1     VIEW OF 'VW_NSO_1'
   3    2       SORT (UNIQUE)
   4    3         UNION-ALL
   5    4           TABLE ACCESS (FULL) OF 'LSC_T2'
   6    4           TABLE ACCESS (FULL) OF 'LSC_T3'
   7    1     TABLE ACCESS (BY INDEX ROWID) OF 'LSC_T1'
   8    7       INDEX (UNIQUE SCAN) OF 'SYS_C003364' (UNIQUE)

;-)

Nicolas.

Randolf Geist · Sep 19, 2008 4:49PM

Interesting subtle differences:

SQL> 
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
PL/SQL Release 9.2.0.8.0 - Production
CORE	9.2.0.8.0	Production
TNS for 32-bit Windows: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production

Elapsed: 00:00:00.01
SQL> 
SQL> drop table lsc_t1;

Table dropped.

Elapsed: 00:00:00.08
SQL> 
SQL> drop table lsc_t2;

Table dropped.

Elapsed: 00:00:00.03
SQL> 
SQL> drop table lsc_t3;

Table dropped.

Elapsed: 00:00:00.03
SQL> 
SQL> create table lsc_t1(a number primary key, b number);

Table created.

Elapsed: 00:00:00.03
SQL> 
SQL> create table lsc_t2(a number primary key, b number);

Table created.

Elapsed: 00:00:00.02
SQL> 
SQL> create table lsc_t3(a number primary key, b number);

Table created.

Elapsed: 00:00:00.02
SQL> 
SQL> insert into lsc_t1(a,b)
  2  select rownum, dbms_random.value from dual connect by level<10000;

9999 rows created.

Elapsed: 00:00:00.19
SQL> 
SQL> insert into lsc_t2(a,b)
  2  select rownum, dbms_random.value from dual connect by level<1000;

999 rows created.

Elapsed: 00:00:00.03
SQL> 
SQL> insert into lsc_t3(a,b)
  2  select rownum, dbms_random.value from dual connect by level<1000;

999 rows created.

Elapsed: 00:00:00.03
SQL> 
SQL> commit;

Commit complete.

Elapsed: 00:00:00.01
SQL> 
SQL> begin
  2    dbms_stats.gather_table_stats(user,'LSC_T1', cascade=>true);
  3    dbms_stats.gather_table_stats(user,'LSC_T2', cascade=>true);
  4    dbms_stats.gather_table_stats(user,'LSC_T3', cascade=>true);
  5  end;
  6  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.11
SQL> 
SQL> set timi on autotrace traceonly echo on
SQL> 
SQL> select --+ INDEX(lsc_t1)
  2  * from lsc_t1
  3  where a in (
  4    select a
  5    from lsc_t2
  6    where b<.01
  7    union all
  8    select a
  9    from lsc_t3
 10    where b<.01
 11  );

20 rows selected.

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=21 Card=19 Bytes=532)
   1    0   NESTED LOOPS (Cost=21 Card=19 Bytes=532)
   2    1     VIEW OF 'VW_NSO_1' (Cost=2 Card=19 Bytes=76)
   3    2       SORT (UNIQUE)
   4    3         UNION-ALL
   5    4           TABLE ACCESS (FULL) OF 'LSC_T2' (Cost=2 Card=9 Bytes=216)
   6    4           TABLE ACCESS (FULL) OF 'LSC_T3' (Cost=2 Card=10 Bytes=240)
   7    1     TABLE ACCESS (BY INDEX ROWID) OF 'LSC_T1' (Cost=1 Card=1 Bytes=24)
   8    7       INDEX (UNIQUE SCAN) OF 'SYS_C003347' (UNIQUE)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         57  consistent gets
          0  physical reads
          0  redo size
       1145  bytes sent via SQL*Net to client
        394  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         20  rows processed

SQL> 
SQL> select
  2  * from lsc_t1
  3  where a in (
  4    select a
  5    from lsc_t2
  6    where b<.01
  7    union all
  8    select a
  9    from lsc_t3
 10    where b<.01
 11  );

20 rows selected.

Elapsed: 00:00:00.15

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=19 Bytes=532)
   1    0   NESTED LOOPS (Cost=8 Card=19 Bytes=532)
   2    1     TABLE ACCESS (FULL) OF 'LSC_T1' (Cost=8 Card=9999 Bytes=239976)
   3    1     VIEW OF 'VW_NSO_1'
   4    3       SORT (UNIQUE)
   5    4         UNION-ALL (PARTITION)
   6    5           TABLE ACCESS (BY INDEX ROWID) OF 'LSC_T2' (Cost=2 Card=1 Bytes=24)
   7    6             INDEX (UNIQUE SCAN) OF 'SYS_C003348' (UNIQUE) (Cost=1 Card=1)
   8    5           TABLE ACCESS (BY INDEX ROWID) OF 'LSC_T3' (Cost=2 Card=1 Bytes=24)
   9    8             INDEX (UNIQUE SCAN) OF 'SYS_C003349' (UNIQUE) (Cost=1 Card=1)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      42043  consistent gets
          0  physical reads
          0  redo size
       1145  bytes sent via SQL*Net to client
        394  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
       9999  sorts (memory)
          0  sorts (disk)
         20  rows processed

SQL> 
SQL> select
  2  * from lsc_t1
  3  where a in (
  4    select a
  5    from lsc_t2
  6    where b<.01
  7    union
  8    select a
  9    from lsc_t3
 10    where b<.01
 11  );

20 rows selected.

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=24 Card=19 Bytes=703)
   1    0   HASH JOIN (Cost=24 Card=19 Bytes=703)
   2    1     VIEW OF 'VW_NSO_1' (Cost=16 Card=19 Bytes=247)
   3    2       SORT (UNIQUE) (Cost=16 Card=19 Bytes=456)
   4    3         UNION-ALL
   5    4           TABLE ACCESS (FULL) OF 'LSC_T2' (Cost=2 Card=9 Bytes=216)
   6    4           TABLE ACCESS (FULL) OF 'LSC_T3' (Cost=2 Card=10 Bytes=240)
   7    1     TABLE ACCESS (FULL) OF 'LSC_T1' (Cost=8 Card=9999 Bytes=239976)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         63  consistent gets
          0  physical reads
          0  redo size
       1145  bytes sent via SQL*Net to client
        394  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         20  rows processed

SQL> 
SQL> select
  2  * from lsc_t1
  3  where a in (
  4    select
  5    distinct a
  6    from (
  7  	 select a
  8  	 from lsc_t2
  9  	 where b<.01
 10  	 union all
 11  	 select a
 12  	 from lsc_t3
 13  	 where b<.01
 14    )
 15  );

20 rows selected.

Elapsed: 00:00:00.03

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=15 Card=1 Bytes=37)
   1    0   HASH JOIN (SEMI) (Cost=15 Card=1 Bytes=37)
   2    1     TABLE ACCESS (FULL) OF 'LSC_T1' (Cost=8 Card=9999 Bytes=239976)
   3    1     VIEW OF 'VW_NSO_1' (Cost=4 Card=19 Bytes=247)
   4    3       VIEW (Cost=4 Card=19 Bytes=247)
   5    4         UNION-ALL
   6    5           TABLE ACCESS (FULL) OF 'LSC_T2' (Cost=2 Card=9 Bytes=216)
   7    5           TABLE ACCESS (FULL) OF 'LSC_T3' (Cost=2 Card=10 Bytes=240)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         63  consistent gets
          0  physical reads
          0  redo size
       1145  bytes sent via SQL*Net to client
        394  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         20  rows processed

Randolf Geist · Sep 19, 2008 4:57PM

Continued due to permanent HTTP 500 error messages when posting as one message...

SQL> 
SQL> select
  2  * from lsc_t1 o
  3  where exists (
  4    select null
  5    from (
  6  	 select a, b
  7  	 from lsc_t2
  8  	 where b<.01
  9  	 union all
 10  	 select a, b
 11  	 from lsc_t3
 12  	 where b<.01
 13    ) i
 14    where i.a = o.a
 15  );

20 rows selected.

Elapsed: 00:00:00.08

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=9999 Bytes=279972)
   1    0   NESTED LOOPS (SEMI) (Cost=8 Card=9999 Bytes=279972)
   2    1     TABLE ACCESS (FULL) OF 'LSC_T1' (Cost=8 Card=9999 Bytes=239976)
   3    1     VIEW
   4    3       UNION-ALL (PARTITION)
   5    4         TABLE ACCESS (BY INDEX ROWID) OF 'LSC_T2' (Cost=2 Card=1 Bytes=24)
   6    5           INDEX (UNIQUE SCAN) OF 'SYS_C003348' (UNIQUE) (Cost=1 Card=1)
   7    4         TABLE ACCESS (BY INDEX ROWID) OF 'LSC_T3' (Cost=2 Card=1 Bytes=24)
   8    7           INDEX (UNIQUE SCAN) OF 'SYS_C003349' (UNIQUE) (Cost=1 Card=1)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      42022  consistent gets
          0  physical reads
          0  redo size
       1145  bytes sent via SQL*Net to client
        394  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         20  rows processed

SQL> 
SQL> select
  2  * from lsc_t1 o
  3  where exists (
  4    select null from (
  5  	 select a, b
  6  	 from lsc_t2
  7  	 union all
  8  	 select a, b
  9  	 from lsc_t3) i
 10    where i.a = o.a
 11    and i.b<.01
 12  );

20 rows selected.

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=28 Card=19 Bytes=931)
   1    0   NESTED LOOPS (Cost=28 Card=19 Bytes=931)
   2    1     SORT (UNIQUE)
   3    2       VIEW (Cost=3 Card=19 Bytes=475)
   4    3         UNION-ALL (PARTITION)
   5    4           TABLE ACCESS (FULL) OF 'LSC_T2' (Cost=2 Card=9 Bytes=216)
   6    4           TABLE ACCESS (FULL) OF 'LSC_T3' (Cost=2 Card=10 Bytes=240)
   7    1     TABLE ACCESS (BY INDEX ROWID) OF 'LSC_T1' (Cost=1 Card=1 Bytes=24)
   8    7       INDEX (UNIQUE SCAN) OF 'SYS_C003347' (UNIQUE)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         57  consistent gets
          0  physical reads
          0  redo size
       1145  bytes sent via SQL*Net to client
        394  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         20  rows processed

SQL> 
SQL> with data as
  2  (
  3  	select *
  4  	from lsc_t2
  5  	   union all
  6  	select *
  7  	from lsc_t3
  8  )
  9  select
 10  	distinct l.*
 11  from lsc_t1 l, data d
 12  where l.a = d.a
 13  and   d.b < .01 ;

20 rows selected.

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=28 Card=19 Bytes=931)
   1    0   SORT (UNIQUE) (Cost=28 Card=19 Bytes=931)
   2    1     NESTED LOOPS (Cost=22 Card=19 Bytes=931)
   3    2       VIEW (Cost=3 Card=19 Bytes=475)
   4    3         UNION-ALL (PARTITION)
   5    4           TABLE ACCESS (FULL) OF 'LSC_T2' (Cost=2 Card=9 Bytes=216)
   6    4           TABLE ACCESS (FULL) OF 'LSC_T3' (Cost=2 Card=10 Bytes=240)
   7    2       TABLE ACCESS (BY INDEX ROWID) OF 'LSC_T1' (Cost=1 Card=1 Bytes=24)
   8    7         INDEX (UNIQUE SCAN) OF 'SYS_C003347' (UNIQUE)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         58  consistent gets
          0  physical reads
          0  redo size
       1145  bytes sent via SQL*Net to client
        394  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         20  rows processed

SQL> 
SQL> select
  2  	distinct l.*
  3  from lsc_t1 l,
  4  (
  5  	select *
  6  	from lsc_t2
  7  	   union all
  8  	select *
  9  	from lsc_t3
 10  ) d
 11  where l.a = d.a
 12  and   d.b < .01 ;

20 rows selected.

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=28 Card=19 Bytes=931)
   1    0   SORT (UNIQUE) (Cost=28 Card=19 Bytes=931)
   2    1     NESTED LOOPS (Cost=22 Card=19 Bytes=931)
   3    2       VIEW (Cost=3 Card=19 Bytes=475)
   4    3         UNION-ALL (PARTITION)
   5    4           TABLE ACCESS (FULL) OF 'LSC_T2' (Cost=2 Card=9 Bytes=216)
   6    4           TABLE ACCESS (FULL) OF 'LSC_T3' (Cost=2 Card=10 Bytes=240)
   7    2       TABLE ACCESS (BY INDEX ROWID) OF 'LSC_T1' (Cost=1 Card=1 Bytes=24)
   8    7         INDEX (UNIQUE SCAN) OF 'SYS_C003347' (UNIQUE)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         58  consistent gets
          0  physical reads
          0  redo size
       1145  bytes sent via SQL*Net to client
        394  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         20  rows processed

SQL> 
SQL> spool off

So when using the CBO Tubby's approach works (and the corresponding "normal" inline view version) as well as the "exists" clause with the filter condition outside the "union all". Depending on the size of the tables the "hash join" used for the "union" or "select distinct" approach also seems to be not too bad.

10.2.0.4 and 11.1.0.6 don't show this behaviour... They only switch between the "good" nested loop plan and the hash join. I haven't checked the 10053 trace yet but I guess 9.2.0.8 somehow loses the cost for the inner nested loop lookup when messing it up.

Regards,
Randolf

Oracle related stuff blog:
http://oracle-randolf.blogspot.com/

SQLTools++ for Oracle (Open source Oracle GUI for Windows):
http://www.sqltools-plusplus.org:7676/
http://sourceforge.net/projects/sqlt-pp/

Edited by: Randolf Geist on Sep 20, 2008 11:16 AM

"Normal" inline view added, 10g + 11g comment

Laurent Schneider · Sep 22, 2008 3:37AM

Thanks for your suggestion.

I tried with cascade=true but it did not help. I also tried with cascade=>true,method_opt=>'for all columns size 254' but it is still not too good.

The rule hint rules, thanks Nicolas. It is however not the expected answer

About rewriting with distinct or unique or union, it is interesting. Still it does not generate the same plan as with /*INDEX(LSC_T1)*/ or /*RULE*/.

Randolf Geist · Sep 22, 2008 4:06AM

Laurent,

if you check my post carefully you'll notice that the "exists" rewrite using the the filter condition outside the "union all" generates a plan that is - although not identical due to the "SORT UNIQUE" operation being outside instead of inside the view definition - from an "execution" perspective "equal" from my point of view. Please let me know if you think this is not the case.

Regards,
Randolf

Oracle related stuff blog:
http://oracle-randolf.blogspot.com/

SQLTools++ for Oracle (Open source Oracle GUI for Windows):
http://www.sqltools-plusplus.org:7676/
http://sourceforge.net/projects/sqlt-pp/

Edited by: Randolf Geist on Sep 22, 2008 10:09 AM

Tubby's solution is not equal due to performing the sort after the NESTED LOOP

Laurent Schneider · Sep 22, 2008 7:22AM

You are right, I missed that one.

Thanks a lot
Laurent

Forum Stats

Quick Links

Categories

Query not using index

Best Answer

Answers

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Categories

Query not using index

Best Answer

Answers