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了