ITPub博客

首页 > 数据库 > Oracle > 关于Inlist iterator操作的一点认识

关于Inlist iterator操作的一点认识

原创 Oracle 作者:talio 时间:2014-09-17 14:51:23 0 删除 编辑

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常常不能如预期地生效,所以使用后还要注意验证结果是否如预期发生了变化。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/27243841/viewspace-1271735/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2013-05-14

  • 博文量
    17
  • 访问量
    273229