關於Inlist iterator操作的一點認識

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 /*+ gather_plan_statistics */ 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 /*+ gather_plan_statistics */ 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 /*+ gather_plan_statistics use_concat */ 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 /*+ gather_plan_statistics use_concat */ 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常常不能如預期地生效，所以使用後還要注意驗證結果是否如預期發生了變化。