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)