Databases Reference
In-Depth Information
Index Skip Scan
An index skip scan occurs when a query "skips" the leading column of a composite index when a query
doesn't specify that column in, say, a
WHERE
clause predicate. The database splits the composite index
into logical subindexes. The fewer the distinct values in the leading column of a composite index and the
larger the distinct values in the other keys that are part of the composite index, the better the
performance of an index skip scan. For example, if the leading column has just three distinct values, the
database divides the composite index into three logical subindexes and searches for the values of the
non-leading indexed columns. Chapter 5 explains the index skip scan in detail in the context of a
composite index.
In the following example, the database uses the composite index
test_idx1
, which was created on
columns
(b, e)
. The query specifies the condition
e=10
. The query's
WHERE
clause doesn't use the leading
column of the composite index, thus skipping that column.
SQL> select b,e from test where e=10;
Execution Plan
----------------------------------------------------------
Plan hash value: 3001938079
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 3 (0)| 00:00:01 |
|* 1 | INDEX SKIP SCAN | TEST_IDX1 | 1 | 7 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E"=10)
filter("E"=10)
SQL>
The number of logical subindexes into which the database splits the composite index will depend
on the number of distinct values of the leading column. In this example, the leading column B of the
composite index has very few distinct values:
SQL> select distinct b from test;
B
----------
1
2
SQL>
As the output shows, there are only two distinct values for the leading column of your composite
index. The database splits the composite index on (b,e) into two subindexes, the first with the key "1"
and the second with the key "2". The database searches the first subindex with the key "1" and then
searches the second subindex with the key "2". The nonleading column of the index, E, on the other
hand, has 10,000 distinct values (same as the number of rows in the table). In cases such as this, the
database finds it cheaper to perform an index skip scan due to the low number of distinct values in the
leading column of the composite index. Note that instead of performing a full table scan when you don't
