关于Inlist iterator操作

http://blog.itpub.net/27243841/viewspace-1271735/

Inlist iterator操作是oracle为inlist查询提供的一种解决方案：

即查询谓词为：where indexed_column in (:1, :2, :3)

对于这种查询，oracle一般有两种解决方法：inlist iterator 和 concatenation.

对于concatenation,就是为inlist中的每个值执行一次索引查询，然后将结果集采用union all的方式合并。

而inlist iterator,oracle的解释是“The INLIST ITERATOR operation iterates over the next operation in the plan for each value in the IN-list predicate.”。从这个解释中恐怕也很理解其内部究竟使用了什么方法来做这种结果的迭代。但按oracle的说法是这种算法更高效。实际测试中也会发现inlist iterator操作的cpu cost和buffer reads更低。

select  NAME  from t1 where id in (1,4,5);

SQL>  sELECT * FROM table(dbms_xplan.display_cursor(null,null,'cost iostats last'));

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SQL_ID  c71htttwfy714, child number 0

-------------------------------------

select  NAME  from t1 where id in (1,4,5) 

Plan hash value: 1916009008 

--------------------------------------------------------------------------------------------------------------

| Id  | Operation                    | Name   | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |

--------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |        |      1 |        |     4 (100)|     30 |00:00:00.01 |      12 |

|   1 |  INLIST ITERATOR             |        |      1 |        |            |     30 |00:00:00.01 |      12 |

|   2 |   TABLE ACCESS BY INDEX ROWID| T1     |      3 |     30 |     4   (0)|     30 |00:00:00.01 |      12 |

|*  3 |    INDEX RANGE SCAN          | IND_T1 |      3 |     30 |     3   (0)|     30 |00:00:00.01 |       8 |

-------------------------------------------------------------------------------------------------------------- 

Predicate Information (identified by operation id):

--------------------------------------------------- 

   3 - access(("ID"=1 OR "ID"=4 OR "ID"=5)) 

20 rows selected.

SQL> select  NAME  from t1 where id in (1,4,5);

SQL> SELECT * FROM table(dbms_xplan.display_cursor(null,null,'cost iostats last')); 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SQL_ID  f2s897x1krsxw, child number 0

-------------------------------------

select  NAME  from t1 where id in (1,4,5)； 

Plan hash value: 1740722098 

-----------------------------------------------------------------------------------------------------------------------

| Id  | Operation                    | Name   | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers | Reads  |

-----------------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |        |      1 |        |     6 (100)|     30 |00:00:00.07 |      13 |      5 |

|   1 |  CONCATENATION               |        |      1 |        |            |     30 |00:00:00.07 |      13 |      5 |

|   2 |   TABLE ACCESS BY INDEX ROWID| T1     |      1 |     10 |     2   (0)|     10 |00:00:00.07 |       5 |      3 |

|*  3 |    INDEX RANGE SCAN          | IND_T1 |      1 |     10 |     1   (0)|     10 |00:00:00.04 |       3 |      2 |

|   4 |   TABLE ACCESS BY INDEX ROWID| T1     |      1 |     10 |     2   (0)|     10 |00:00:00.01 |       5 |      0 |

|*  5 |    INDEX RANGE SCAN          | IND_T1 |      1 |     10 |     1   (0)|     10 |00:00:00.01 |       3 |      0 |

|   6 |   TABLE ACCESS BY INDEX ROWID| T1     |      1 |     10 |     2   (0)|     10 |00:00:00.02 |       3 |      2 |

|*  7 |    INDEX RANGE SCAN          | IND_T1 |      1 |     10 |     1   (0)|     10 |00:00:00.01 |       2 |      1 |

-----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   3 - access("ID"=5)

   5 - access("ID"=4)

   7 - access("ID"=1) 

27 rows selected.

 

其他：

a>  若希望禁用inlist iterator,可设置10157等待事件：

alter session set events ‘10157 trace name context forever, level 1’; 

b>  use_concat也可用来禁用inlist iterator操作，而强制其使用concatenation操作。但实际中会发现该hint常常不能如预期地生效，所以使用后还要注意验证结果是否如预期发生了变化。