Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Query not using index

Laurent SchneiderSep 19 2008 — edited Sep 22 2008
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)
This post has been answered by Randolf Geist on Sep 22 2008
Jump to Answer

Comments

Processing
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Oct 20 2008
Added on Sep 19 2008
8 comments
585 views