跳躍式索引(Skip Scan Index)的淺析
2024-07-21 02:32:42
供稿：網友
在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 onSQL> select /*+index_ss(test1 test_idx1)*/* from test1 a2 where b ='a';no rows selectedExecution 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> begin2 for i in 1..100000 loop3 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 eXPlainSQL> select * from test12 where b = '500';A B C---------- ---------- ----------19 500 500Execution 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> begin2 for i in 1..100000 loop3 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 500Execution 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 Bytes=17)2 1 INDEX (SKIP SCAN) OF 'TEST_IDX1' (NON-UNIQUE) (Cost=37 Card=1)從上面試驗結果看，FTS的cost是37。當第一列distinct number小于這個值時，Oracle選擇了SS。繼續試驗：SQL> select count(*) from test12 where b <= '1';COUNT(*)----------1Execution 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 Card=1 Bytes=10)注重：在b中’10’是比’1’大的最小值（char(10)類型）SQL> select count(*) from test12 where b <= '10';COUNT(*)----------2Execution 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=7730)觀察結果，這時候影響的因素是cardinality了。第二個查詢計劃中的cardinality值（773）正是b<=’10’的cardinality值：
SQL> set autotrace offSQL> select 100000*(to_number('31302020202020202020', 'xxxxxxxxxxxxxxxxxxxx')-to_number('31202020202020202020', 'xxxxxxxxxxxxxxxxxxxx'))/(to_number('39393939392020202020', 'xxxxxxxxxxxxxxxxxxxx')-to_number('31202020202020202020', 'xxxxxxxxxxxxxxxxxxxx'))+1 from dual;100000*(TO_NUMBER('31302020202020202020','XXXXXXXXXXXXXXXXXXXX')-TO_NUMBER('3120--------------------------------------------------------------------------------772.791768再看一個含有第一列條件的等效的語句：SQL> set autotrace on explainSQL> select count(*) from test12 where a>=03 and b <='1';COUNT(*)----------1Execution 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 Card=1 Bytes=12) 再做幾個有趣的試驗，下面的試驗條件是不滿足SS的，但是請注重查詢返回列隊查詢計劃的影響：SQL> truncate table test1; Table truncated.SQL> begin2 for i in 1..100000 loop3 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 500Execution 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 test12 where b = '500';COUNT(*)----------1Execution 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 test12 where b = '500';A----------500Execution Plan----------------------------------------------------------0 SELECT STATEMENT Optimizer=CHOOSE (Cost=34 Card=1 Bytes=14)1 0 INDEX (FAST FULL SCAN) OF 'TEST_IDX1' (NON-UNIQUE) (Cost=34 Card=1 Bytes=14)使用RBO呢？SQL> select /*+rule*/a from test12 where b = '500';A----------500Execution Plan----------------------------------------------------------0 SELECT STATEMENT Optimizer=HINT: RULE1 0 TABLE ACCESS (FULL) OF 'TEST1'
值得一提的是，上述任何一個例子在8i中執行的話，都不會使用到索引（無論是否符合SS的條件）。
Trackback: http://tb.blog.csdn.net/TrackBack.aspx?PostId=1479527
