文档说明:
When the Optimizer Uses IN-List Iterators
The optimizer uses an IN-list iterator when an IN clause is specified with values, and the optimizer finds a selective index for that column.
If there are multiple OR clauses using the same index, then the optimizer chooses this operation rather than CONCATENATION or UNION ALL,
because it is more efficient.
下面来看几种情况:
测试环境:
SQL> create table t1 as select * from dba_objects;
表已创建。
SQL> set linesize 200 pagesize 200
1、表上object_id字段没有索引
SQL> select /*+gather_plan_statistics*/ object_name,object_id from t1 where object_id in (100,200,300,400,500,600);
OBJECT_NAME OBJECT_ID
-------------------------------- ----------
ORA$BASE 100
I_SQL$TEXT_PKEY 200
RESOURCE_CAPABILITY$ 300
ENC$ 400
OID$ 500
SQL> select * from table(dbms_xplan.display_cursor(null,null,'iostats last'));
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/24496749/viewspace-730530/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/24496749/viewspace-730530/