Forum Stats

  • 3,767,751 Users
  • 2,252,713 Discussions
  • 7,874,327 Comments

Discussions

Query not using index

Laurent Schneider
Laurent Schneider Member Posts: 5,219 Bronze Badge
edited Sep 22, 2008 7:22AM in SQL & PL/SQL
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)
Tagged:

Best Answer

  • Randolf Geist
    Randolf Geist Member Posts: 2,214 Silver Trophy
    edited Sep 22, 2008 4:09AM Accepted Answer
    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

Answers

  • Tubby
    Tubby Member Posts: 6,987 Bronze Crown
    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 ;
    Tubby
  • Karthick2003
    Karthick2003 Member Posts: 13,711 Bronze Badge
    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.
    Karthick2003
  • Nicolas Gasparotto
    Nicolas Gasparotto Member Posts: 25,514 Silver Crown
    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.
    Nicolas Gasparotto
  • Randolf Geist
    Randolf Geist Member Posts: 2,214 Silver Trophy
    edited Sep 19, 2008 4:54PM
    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
    Randolf Geist Member Posts: 2,214 Silver Trophy
    edited Sep 20, 2008 6:32AM
    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
    Laurent Schneider Member Posts: 5,219 Bronze Badge
    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
    Randolf Geist Member Posts: 2,214 Silver Trophy
    edited Sep 22, 2008 4:09AM Accepted Answer
    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
    Laurent Schneider Member Posts: 5,219 Bronze Badge
    You are right, I missed that one.

    Thanks a lot
    Laurent
This discussion has been closed.