IN-List Iterator

The 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 '10157  TRACE 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，到底迭代了多少次，因为一次迭代就意味着一次表的访问。

IN-List Iterator

The 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 '10157  TRACE 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，到底迭代了多少次，因为一次迭代就意味着一次表的访问。