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!

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

top.gun

OMG - where do I start!

If you are using Oracle for commercial purposes then you or your client needs a licence from Oracle. So really you should stop now.

Once you have your licence from Oracle, you can do a simulated import (ie show metadata) like this:

imp ar/live show=y statistics=none buffer=100000 feedback=100000 log=/gers/live/tmp/CUST_190312.log file=/gers/live/tmp/CUST_190312.dmp

Then if the above works you can do the real import (ie metadata and data) like this:

imp ar/live full=y statistics=none buffer=100000 feedback=100000 log=/gers/live/tmp/CUST_190312.log file=/gers/live/tmp/CUST_190312.dmp

Dean Gagne-Oracle

You can always use

imp help=y

to give you the available commands and options.  If you only want the data, then you can use:

imp user/password file=your_file.dmp rows=y

Hope this helps.

Dean

Lisa-Oracle

Hi AJVinkles,

You need to add ignore=y since the table pre-exists

Using IGNORE=y instructs Oracle to ignore any creation errors during the import

and permit the import to complete.

imp user/password file=  log= FROMUSER= TABLES=  ignore=y

rows=y as stated above is the default so it is not needed in the command

Regards,

Lisa

1 - 3
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
590 views