INDEX SKIP SCAN
Index operation.
INDEX SKIP SCAN selects rows from a concatenated index without using the leading columns in the index. This feature was introduced in Oracle 9i.
INDEX SKIP SCAN DESCENDING
The INDEX SKIP SCAN DESCENDING operation retrieves rows from a concatenated index without using the leading column in descending order.
INDEX UNIQUE SCAN
Execution Plan image: 
Index operation.
INDEX UNIQUE SCAN, which selects a unique value from a unique index, is the most efficient method of selecting a row from known field values.
Each unique index access is built from a separate access into the index’s B*-tree structure, drilling down from the index root to the leaf blocks. On average, three blocks are read to fulfill the unique index access.
Example
select Name, City, State
from COMPANY
where Company_ID = 12345;
Execution Plan
TABLE ACCESS BY ROWID COMPANY
INDEX UNIQUE SCAN COMPANY_PK
Interpreting the Execution Plan
The query uses the COMPANY_ID column as the sole criteria in its WHERE clause. Since COMPANY_ID is the primary key of the COMPANY table, it has a unique index associated with it. The unique index for the COMPANY_ID primary key is named COMPANY_PK.
During the query, the COMPANY_PK index is scanned for one COMPANY_ID value (12345). When the COMPANY_ID value is found, the ROWID associated with that COMPANY_ID is used to query the COMPANY table.
Tuning Advice
Avoid Unhelpful Index Scans
INLIST ITERATOR
Execution Plan image:
Row operation.
An INLIST ITERATOR operation displays in the Execution Plan output if an index implements an INLIST predicate.
Example
SELECT * FROM emp WHERE empno IN (7876, 7900, 7902);
Execution Plan
|
Operation |
Options |
Object_name |
|
SELECT STATEMENT |
|
|
|
INLIST ITERATOR |
|
|
|
TABLE ACCESS |
BY ROWID |
EMP |
|
INDEX |
RANGE SCAN |
EMP_EMPNO |
The INLIST ITERATOR operation iterates over the operation below it for each value in the IN-list predicate. For partitioned tables and indexes, the three possible types of INLIST columns are:
-
Index column
-
Index and partition column
-
Partition column
Index Column
When the INLIST column empno is an index column but not a partition column, the plan is as follows (the INLIST operator displays above the table operation but below the partition operation):
|
Operation |
Options |
Object_name |
Partition Start |
Partition Stop |
|
SELECT STATEMENT |
|
|
|
|
|
PARTITION |
INLIST |
|
KEY(INLIST) |
KEY(INLIST) |
|
INLIST ITERATOR |
|
|
|
|
|
TABLE ACCESS |
BY ROWID |
EMP |
KEY(INLIST) |
KEY(INLIST) |
|
INDEX |
RANGE SCAN |
EMP_EMPNO |
KEY(INLIST) |
KEY(INLIST) |
The KEY(INLIST) designation for the partition start and stop keys specifies that an INLIST predicate displays on the index start/stop keys.
Index and Partition Columns
When empno is an indexed and a partition column, the Execution plan contains an INLIST ITERATOR operation above the partition operation:
|
Operation |
Options |
Object_name |
Partition Start |
Partition Stop |
|
SELECT STATEMENT |
|
|
|
|
|
INLIST ITERATOR |
|
|
|
|
|
PARTITION |
ITERATOR |
|
KEY(INLIST) |
KEY(INLIST) |
|
TABLE ACCESS |
BY ROWID |
EMP |
KEY(INLIST) |
KEY(INLIST) |
|
INDEX |
RANGE SCAN |
EMP_EMPNO |
KEY(INLIST) |
KEY(INLIST) |
Partition Column
When empno is a partition column and there are no indexes, no INLIST ITERATOR operation is allocated:
|
Operation |
Options |
Object_name |
Partition Start |
Partition Stop |
|
SELECT STATEMENT |
|
|
|
|
|
PARTITION |
|
|
KEY(INLIST) |
KEY(INLIST) |
|
TABLE ACCESS |
BY ROWID |
EMP |
KEY(INLIST) |
KEY(INLIST) |
|
INDEX |
RANGE SCAN |
EMP_EMPNO |
KEY(INLIST) |
KEY(INLIST) |
When emp_empno is a bitmap index, the Execution plan is as follows:
|
Operation |
Options |
Object_name |
|
SELECT STATEMENT |
|
|
|
INLIST ITERATOR |
|
|
|
TABLE ACCESS |
BY INDEX ROWID |
EMP |
|
BITMAP CONVERSION |
|
TO ROWIDS |
|
BITMAP INDEX |
SINGLE VALUE |
EMP_EMPNO |