in-list iterator

in-list iterator

--针对目标sql的in后面是常量集合的首选项处理方法，其处理效率通常都会比in-list expansion高
--使用in-list iterator的时候，in所在列上一定要有索引
--可以通过联合设置10142和10157事件来禁掉in-list iterator，但是没有hint来强制使用in-list iterator

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

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是等价的：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

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=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 | ----------------------------------------------------------------------------------------------

　　

删除索引后就会走全表扫描，用不到INLIST ITERATOR的：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

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