in-list iterator

时间：2024-11-30 22:33:56

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

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.****.net/zengxuewen2045/article/details/52017297

相关文章