关于IN-List Iterator执行计划的解读
IN-List IteratorThe IN-list iterator function compares each row in the row source with each value from the IN-list.
The column compared with the IN-list must be indexed to use the IN-list Iterator function.
Typically, the IN-list iterator is more efficient than OR expansion.
There is no hint to force the IN-list iterator,but you can use Event 10157 to disable it.
The IN-list iterator feeds the equality clause with unique values from the IN-list. It
iterates over the values in the IN-list for each row returned. It is far more efficient
than OR expansion.
SQL> CREATE TABLE T AS SELECT * FROM ALL_OBJECTS WHERE ROWNUM<=10000;
Table created.
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'T',cascade=>true);
PL/SQL procedure successfully completed.
SQL> SET AUTOT TRACEONLY EXP
SQL> SELECT * FROM T WHERE OBJECT_ID IN (1023,4567);
Execution Plan
----------------------------------------------------------
Plan hash value: 2153619298
--------------------------------------------------------------------------
| Id| Operation | Name | Rows| Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT| | 2 | 168 | 27 (4)| 00:00:01 |
|*1 |TABLE ACCESS FULL| T | 2 | 168 | 27 (4)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=1023 OR "OBJECT_ID"=4567)
如果没有在谓词列上创建相关索引，那么是用不到INLIST ITERATOR的。
SQL> CREATE UNIQUE INDEX T_IDX ON T(OBJECT_ID);
Index created.
SQL> SELECT * FROM T WHERE OBJECT_ID IN (1023,4567);
Execution Plan
----------------------------------------------------------
Plan hash value: 1765020610
--------------------------------------------------------------------------------------
| Id| Operation | Name| Rows| Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 168 | 3 (0)| 00:00:01 |
| 1 |INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 2 | 168 | 3 (0)| 00:00:01 |
|*3 | INDEX UNIQUE SCAN | T_IDX | 2 | | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("OBJECT_ID"=1023 OR "OBJECT_ID"=4567)
我们可以通过，10517事件禁止使用INLIST ITERATOR。
SQL> ALTER SESSION SET EVENTS '10157TRACE NAME CONTEXT FOREVER,LEVEL 1';
Session altered.
SQL>SELECT * FROM T WHERE OBJECT_ID IN (1023,4567);
Execution Plan
----------------------------------------------------------
Plan hash value: 715947217
------------------------------------------------------------------------------------------
| Id| Operation | Name| Rows| Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 168 | 2 (0)| 00:00:01 |
| 1 |TABLE ACCESS BY INDEX ROWID | T | 2 | 168 | 2 (0)| 00:00:01 |
| 2 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 3 | BITMAP OR | | | | | |
| 4 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|*5 | INDEX RANGE SCAN | T_IDX | | | 1 (0)| 00:00:01 |
| 6 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|*7 | INDEX RANGE SCAN | T_IDX | | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("OBJECT_ID"=1023)
7 - access("OBJECT_ID"=4567)
我们在看执行计划的时候，如果发现了IN-List Iterator，要引起我们的注意。
这个执行计划往往是因为IN操作引起的，如果索引没有或者设计有问题，那么可能带来严重的性能问题。
我们要关注IN-List Iterator，到底迭代了多少次，因为一次迭代就意味着一次表的访问。
页:
[1]