跳躍式索引(Skip Scan Index)的淺析

在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 ptimizer=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 ptimizer=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 ptimizer=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 ptimizer=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 ptimizer=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 ptimizer=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 ptimizer=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 ptimizer=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 ptimizer=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 ptimizer=HINT: RULE

   1    0   TABLE ACCESS (FULL) OF 'TEST1'

值得一提的是，上述任何一個例子在8i中執行的話，都不會使用到索引（無論是否符合SS的條件）。