in-list iterator-白红宇

in-list iterator

阅读量：4312 次

发布时间：2019-06-06

本文共 3566 字，大约阅读时间需要 11 分钟。

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优化示例：

转载于:https://www.cnblogs.com/abclife/p/5992199.html

你可能感兴趣的文章

[BZOJ 3530] [Sdoi2014] 数数【AC自动机+DP】