一個很簡單的查詢,為什麼用不到索引

http://www.itpub.net/viewthread.php?tid=964719&extra=&page=1

create table test  as select rownum id,rownum-1 id2 from dba_objects;

create index idx_test1 on test(id2);

analyze table test compute statistics for all indexes;

SQL> set autot on
SQL> select id  from test
  2  where
  3  id2 in
  4     (
  5    select  '2' from dual
  6      );

        ID
----------
         3


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=30 Card=8168 Bytes=7
          3512)

   1    0   HASH JOIN (Cost=30 Card=8168 Bytes=73512)
   2    1     TABLE ACCESS (FULL) OF 'TEST' (Cost=3 Card=6173 Bytes=37
          038)

   3    1     VIEW OF 'VW_NSO_1' (Cost=24 Card=8168 Bytes=24504)
   4    3       SORT (UNIQUE) (Cost=24 Card=8168)
   5    4         TABLE ACCESS (FULL) OF 'DUAL' (Cost=11 Card=8168)




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         19  consistent gets
          0  physical reads
          0  redo size
        373  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select id  from test
  2  where
  3  id2 in
  4     (
  5     '2','3'
  6      );

        ID
----------
         3
         4


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=3 Card=2 Bytes=12)
   1    0   INLIST ITERATOR
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=3 Card=2 B
          ytes=12)

   3    2       INDEX (RANGE SCAN) OF 'IDX_TEST1' (NON-UNIQUE) (Cost=2
           Card=2)





Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        408  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed

SQL>
對dual表進行分析後

會用上索引

因為dual是系統表一分析可能會其他系統表用到,會有影響

想到方法是將dual表改為其他表

create table t (id number);

analyze table t compute statistics;

SQL> select   id   from test
  2  where
  3  id2 in
  4     (
  5     select '2'  from t
  6      );

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=6 Card=1 Bytes=9)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=2 Card=1 Byt
          es=6)

   2    1     NESTED LOOPS (Cost=6 Card=1 Bytes=9)
   3    2       VIEW OF 'VW_NSO_1' (Cost=4 Card=1 Bytes=3)
   4    3         SORT (UNIQUE) (Cost=4 Card=1)
   5    4           TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=1)
   6    2       INDEX (RANGE SCAN) OF 'IDX_TEST1' (NON-UNIQUE) (Cost=1
           Card=1)

也用上索引了

也可以用下面這種方法:

select id  from test
id2 in
   (
   select '2'  from dual where rownum < 2
    )
也會用上索引

樓主用的方法是把相關表統計資料刪除掉,完全用RBO了