在Oracle9i中，有一個新的特性：跳躍式索引(Skip Scan Index)。當表有一個復合索引，而在查詢中有除了索引中第一列的其他列作為條件，並且優化器模式為CBO，這時候查詢計劃就有可能使用到SS。此外，還可以通過使用提示index_ss（CBO下）來強制使用SS。
舉例：
SQL> create table test1 (a number, b char(10), c varchar2(10));
Table created.
SQL> create index test_idx1 on test1(a, b);
Index created.
SQL> set autotrace on
SQL> select /*+index_ss(test1 test_idx1)*/* from test1 a
2 where b ='a';
no rows selected
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=32)
1 0 TABLE Access (BY INDEX ROWID) OF 'TEST1' (Cost=2 Card=1 Bytes=32)
2 1 INDEX (SKIP SCAN) OF 'TEST_IDX1' (NON-UNIQUE)
但並不是任何情況下都會使用到SS。在Oracle的官方文檔中，除了提到需要CBO，並且對表進行過分析外，還需要保證第一列的distinct value非常小。這一段是從官方文檔上摘取的關於SS的一段解釋：
Index skip scans improve index scans by nonprefix columns since it is often faster to scan index blocks than scanning table data blocks.
In this case a composite index is split logically into smaller subindexes. The number of logical subindexes depends on the cardinality of the initial column. Hence it is now possible to use the index even if the leading column is not used in a where clause.
Oracle並沒有公布過關於SS更多的內部技術細節。但注意上面的這句話：In this case a composite index is split logically into smaller subindexes. The number of logical subindexes depends on the cardinality of the initial column.即Oralce會對復合索引進行邏輯劃分，分成多個子索引。可以這樣理解，Oracle將索引從邏輯上劃分為a.num_distinct個子索引，每次對一個子索引進行掃描。因此SS的索引掃描成本為a.num_distinct.
下面做一些試驗,看看在什麼情況下Oracle采用SS.
首先要保證使用SS的幾個必要條件:
· Optimizer為CBO
· 相關表要有正確的統計數據
· Oracle DB版本為9i以上
下面就是一個使用到SS的特殊條件：第一列的distinct num要足夠小。小到什麼程度呢？
還是以上面的表為例（省略中間的麻煩步驟，取兩個臨界值做實驗）：
取第一列distinct number為37：
SQL> truncate table test1;
Table truncated.
SQL> begin
2 for i in 1..100000 loop
3 insert into test1 values (mod(i,37), to_char(i), to_char(i));
4 end loop;p;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> analyze table test1 compute statistics;
Table analyzed.
SQL> set autotrace on explain
SQL> select * from test1
2 where b = '500';
A B C
---------- ---------- ----------
19 500 500
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=37 Card=1 Bytes=17)
1 0 TABLE Access (FULL) OF 'TEST1' (Cost=37 Card=1 Bytes=17)
再取第一列distinct number為36：
SQL> truncate table test1;
Table truncated.
SQL> begin
2 for i in 1..100000 loop
3 insert into test1 values (mod(i,36), to_char(i), to_char(i));
4 end loop;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> analyze table test1 compute statistics;
Table analyzed.
SQL> select * from test1 where b = '500';
A B C
---------- ---------- ----------
32 500 500
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=12 Card=1 Bytes=17)
1 0 TABLE Access (BY INDEX ROWID) OF 'TEST1' (Cost=12 Card=1 B
ytes=17)
2 1 INDEX (SKIP SCAN) OF 'TEST_IDX1' (NON-UNIQUE) (Cost=37 C
ard=1)
從上面試驗結果看，FTS的cost是37。當第一列distinct number小於這個值時，Oracle選擇了SS。
繼續試驗：
SQL> select count(*) from test1
2 where b <= '1';
COUNT(*)
----------
1
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=12 Card=1 Bytes=10)
1 0 SORT (AGGREGATE)
2 1 INDEX (SKIP SCAN) OF 'TEST_IDX1' (NON-UNIQUE) (Cost=37 C
ard=1 Bytes=10)
注意：在b中’10’是比’1’大的最小值（char(10)類型）
SQL> select count(*) from test1
2 where b <= '10';
COUNT(*)
----------
2
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=37 Card=1 Bytes=10)
1 0 SORT (AGGREGATE)
2 1 TABLE Access (FULL) OF 'TEST1' (Cost=37 Card=773 Bytes=7
730)
觀察結果，這時候影響的因素是cardinality了。第二個查詢計劃中的cardinality值（773）正是b<=’10’的cardinality值：
SQL> set autotrace off
SQL> select 100000*(to_number('31302020202020202020', 'xxxxxxxxxxxxxxxxxxxx')-to
_number('31202020202020202020', 'xxxxxxxxxxxxxxxxxxxx'))/(to_number('39393939392
020202020', 'xxxxxxxxxxxxxxxxxxxx')-to_number('31202020202020202020', 'xxxxxxxxx
xxxxxxxxxxx'))+1 from dual;
100000*(TO_NUMBER('31302020202020202020','XXXXXXXXXXXXXXXXXXXX')-TO_NUMBER('3120
--------------------------------------------------------------------------------
772.791768
再看一個含有第一列條件的等效的語句：
SQL> set autotrace on explain
SQL> select count(*) from test1
2 where a>=0
3 and b <='1';
COUNT(*)
----------
1
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=12 Card=1 Bytes=12)
1 0 SORT (AGGREGATE)
2 1 INDEX (SKIP SCAN) OF 'TEST_IDX1' (NON-UNIQUE) (Cost=37 C
ard=1 Bytes=12)
再做幾個有趣的試驗，下面的試驗條件是不滿足SS的，但是請注意查詢返回列隊查詢計劃的影響：
SQL> truncate table test1;
Table truncated.
SQL> begin
2 for i in 1..100000 loop
3 insert into test1 values (i, to_char(i), to_char(i));
4 end loop;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> analyze table test1 compute statistics;
Table analyzed.
SQL> select * from test1
2 where b = '500';
A B C
---------- ---------- ----------
500 500 500
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=37 Card=1 Bytes=19)
1 0 TABLE Access (FULL) OF 'TEST1' (Cost=37 Card=1 Bytes=19)
改變返回列：
SQL> select count(*) from test1
2 where b = '500';
COUNT(*)
----------
1
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=34 Card=1 Bytes=10)
1 0 SORT (AGGREGATE)
2 1 INDEX (FAST FULL SCAN) OF 'TEST_IDX1' (NON-UNIQUE) (Cost
=34 Card=1 Bytes=10)
再改變一種：
SQL> select a from test1
2 where b = '500';
A
----------
500
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=34 Card=1 Bytes=14)
1 0 INDEX (FAST FULL SCAN) OF 'TEST_IDX1' (NON-UNIQUE) (Cost=3
4 Card=1 Bytes=14)
使用RBO呢？
SQL> select /*+rule*/a from test1
2 where b = '500';
A
----------
500
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: RULE
1 0 TABLE Access (FULL) OF 'TEST1'
值得一提的是，上述任何一個例子在8i中執行的話，都不會使用到索引（無論是否符合SS的條件）。
