[轉貼]Skip Scan Index

今天用STA，叫我用Skip Scan Index ，轉個貼記錄了一下

[@more@]

在Oracle9i中，有一個新的特性：跳躍式索引(Skip Scan Index)。當表有一個複合索引，而在查詢中有除了索引中第一列的其他列作為條件，並且最佳化器模式為CBO，這時候查詢計劃就有可能使用到SS。此外，還可以透過使用提示index_ss（CBO下）來強制使用SS。
強制使用ss的方法： select /*+index_ss(test1 test_idx1)*/* from test1 a where b ='a';

但並不是任何情況下都會使用到SS。在Oracle的官方文件中，除了提到需要CBO，並且對錶進行過分析外，還需要保證第一列的distinct value非常小。INDEX SKIP SCAN是需要下面幾個條件的：
1，版本9i及以後
2，CBO（表和索引都經過分析）
3，索引的前導列重複值很少（選擇性低）
4，WHERE條件中不存在索引前導列

舉例：
1， 建立測試環境
Sql>select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.1.0 - Productio
NLSRTL Version 10.2.0.1.0 - Production
Sql>conn sunwg/test
已連線
Sql>create table sunwg as select * from dba_objects;
表已建立
Sql>create index ind_type_id on sunwg(object_type,object_id);
索引已建立
3，一般情況下，WHERE條件中包含索引的前導列，那麼查詢是會選擇索引的
4，如果WHERE中不包括索引的前導列，在沒有統計資訊的情況下，最佳化器是不會選擇索引的。
5，收集了表和索引的統計資訊，那麼就會執行索引的跳躍掃描
從兩次執行時候的COST可以看得出來，執行索引跳躍掃描的時候效率會比全表掃描高不少。索引跳躍掃描具體是怎麼執行的呢？
從網上查了資料，說是索引跳躍掃描實際上轉化成為多個SQL的UNION操作，對於上面的例子，可能大概轉化為下面的形式：
Select * from sunwg where object_type = ‘TABLE’ and object_id = 68191
Union
Select * from sunwg where object_type = ‘INDEX’ and object_id =68191
Union
……
我們有驗證這種說法的正確性，但是利用這種說法可以很好的解釋為什麼索引跳躍掃描僅僅發生在前導列的重複值很多的情況下。因為如果索引前導列的重複值很少，那麼將會轉化成大量SQL的UNION操作，這樣效率相對來說會低得多。
上面就是大家經常的對索引跳躍掃描的理解，覺得索引跳躍掃描相比全表掃描來說有著更好的效能。但是很少想到，索引跳躍掃描真是很好的事情麼？
從兩方面來說吧
1， 當前導列的重複值很多的時候，ORACLE並不會選擇索引的跳躍掃描，他認為這樣的效率設定比全表掃描還要低。可是實際上，ORACLE並沒有把第二索引列考慮進去，如果第二個索引列的選擇性很高的情況下，走索引可能還是一種高效的訪問方式。實驗很簡單，這裡就不做了，感興趣的可以自己實驗下。
2， 前面說過了，只有前導列的重複值很小的時候，ORACLE才會選擇索引跳躍掃描。其實索引跳躍掃描僅僅是對低效索引的一種彌補。從B+樹索引的結構可以看得出來，索引列的選擇性越高那麼索引的效果越好。那麼在建多列索引中的時候也應該把選擇性高的列放到前面，這樣可以極大的提高的索引訪問的效率。所以當我們的SQL 走了索引跳躍掃描的時候，我們應該多想想是不是我們的索引建立的有問題。
ORACLE新版本的一些新的特性，有一些是原來沒有東西，有一些是對原有的有問題的東西的修補，那麼我們在使用新特性的時候要多想想，多比比，這樣才可以合理的使用那些新的東西。

附記： STA好像有問題，推薦我用 Skip Scan Index 記錄裡面 cost只需要 381 ，可以我用提示來強制使用後，執行計劃一樣的，cost卻變成了138367 ,這個。。。 差的也太遠了吧