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)