in-list iterator
–针对目标sql的in后面是常量集合的首选项处理方法，其处理效率通常都会比in-list expansion高
–使用in-list iterator的时候，in所在列上一定要有索引
–可以通过联合设置10142和10157事件来禁掉in-list iterator，但是没有hint来强制使用in-list iterator
SQL> create table emp1 as select * from emp;
SQL> create index idx_emp1_dept on emp1(deptno);
SQL> select * from emp1 where deptno in (10,20,30);
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
SQL_ID df7dw3ph8g0t7, child number 0
-------------------------------------
select * from emp1 where deptno in (10,20,30)Plan hash value: 2544692611----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP1 | 14 | 1218 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX_EMP1_DEPT | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
oracle中in和or是等价的：
SQL> select * from emp1 where deptno=10 or deptno=20 or deptno=30;
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
SQL_ID 2yya3cuswm15m, child number 0
-------------------------------------
select * from emp1 where deptno=10 or deptno=20 or deptno=30Plan hash value: 2544692611----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP1 | 14 | 1218 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX_EMP1_DEPT | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
删除索引后就会走全表扫描，用不到INLIST ITERATOR的：
SQL> drop index idx_emp1_dept;
SQL> select * from emp1 where deptno in (10,20,30);
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
SQL_ID df7dw3ph8g0t7, child number 0
-------------------------------------
select * from emp1 where deptno in (10,20,30)Plan hash value: 2226897347--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | TABLE ACCESS FULL| EMP1 | 14 | 1218 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
使用in-list iterator优化示例：http://blog.csdn.net/zengxuewen2045/article/details/52017297
