Oracle Index Skip Scans使用场景

INDEX跳跃扫描一般用在WHERE条件里面没有使用到引导列，但是用到了引导列以外的其他列，并且引导列的DISTINCT值较少的情况。

在这种情况下，数据库把这个复合索引逻辑上拆散为多个子索引，依次搜索子索引中非引导列的WHERE条件里面的值。

使用方法如下:

/*+ INDEX_SS ( [ @ qb_name ] tablespec [ indexspec [ indexspec ]... ] ) */

The 

INDEX_SS

hint instructs the optimizer to perform an index skip scan for the specified table. If the statement uses an index range scan, then Oracle scans the index entries in ascending order of their indexed values. In a partitioned index, the results are in ascending order within each partition.Each parameter serves the same purpose as in "INDEX Hint". For example:

SELECT /*+ INDEX_SS(e emp_name_ix) */ last_name FROM employees e WHERE first_name = 'Steven';

下面是来自ORACLE PERFORMANCE TUNING里的原文：

Index skip scans improve index scans by nonprefix columns. Often, scanning index blocks is faster than scanning table data blocks.

Skip scanning lets a composite index be split logically into smaller subindexes. In skip scanning, the initial column of the composite index is not specified in the query. In other words, it is skipped.

The number of logical subindexes is determined by the number of distinct values in the initial column. Skip scanning is advantageous if there are few distinct values in the leading column of the composite index and many distinct values in the nonleading key of the index.

Example 13-5 Index Skip Scan

Consider, for example, a table 

employees

 (

sex

, 

employee_id

, 

address

) with a composite index on (

sex

, 

employee_id

). Splitting this composite index would result in two logical subindexes, one for 

M

 and one for 

F

.

For this example, suppose you have the following index data:

('F',98)('F',100)('F',102)('F',104)('M',101)('M',103)('M',105)

The index is split logically into the following two subindexes:

• The first subindex has the keys with the value 

  F

  .

• The second subindex has the keys with the value 

  M

  .

• The column 

  sex

   is skipped in the following query:

  SELECT * FROM employeesWHERE employee_id = 101;

  A complete scan of the index is not performed, but the subindex with the value 

  F

   is searched first, followed by a search of the subindex with the value 

  M

  .