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 (
). 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
the second subindex has the keys with the value
the column
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
is searched first, followed by a search of the subindex with the value