在Oracle9i中我們知道能夠使用跳躍式索引掃描(Index Skip Scan).然而,能利用跳躍式索引掃描的情況其實(shí)是有些限制的.
從Oracle的文檔中我們可以找到這樣的話:
Index Skip Scans
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.
也可以這樣說(shuō),優(yōu)化器根據(jù)索引中的前導(dǎo)列(索引到的第一列)的唯一值的數(shù)量決定是否使用Skip Scan.
我們首先做個(gè)測(cè)試：
SQL> CREATE TABLE test AS
2 SELECT ROWNUM a,ROWNUM-1 b ,ROWNUM-2 c,ROWNUM-3 d,ROWNUM-4 e
3 FROM all_objects
4 /
SQL> SELECT DISTINCT COUNT (a) FROM test;
COUNT(A)
----------
28251
表已創(chuàng)建。
SQL>
SQL> CREATE INDEX test_idx ON test(a,b,c)
2 /
索引已創(chuàng)建。
SQL> ANALYZE TABLE test COMPUTE STATISTICS
2 FOR TABLE
3 FOR ALL INDEXES
4 FOR ALL INDEXED COLUMNS
5 /
表已分析。
SQL> SET autotrace traceonly eXPlain
SQL> SELECT * FROM test WHERE b = 99
2 /
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=36 Card=1 Bytes=26)
1 0 TABLE access (FULL) OF 'TEST' (Cost=36 Card=1 Bytes=26)
--可見(jiàn)這里CBO選擇了全表掃描.
--我們接著做另一個(gè)測(cè)試:
SQL> drop table test;
表已丟棄。
SQL> CREATE TABLE test
2 AS
3 SELECT DECODE(MOD(ROWNUM,2), 0, '1', '2' ) a,
4 ROWNUM-1 b,
5 ROWNUM-2 c,
6 ROWNUM-3 d,
7 ROWNUM-4 e
8 FROM all_objects
9 /
表已創(chuàng)建。
SQL> set autotrace off
SQL> select distinct a from test;
A
--
1
2
--A列只有兩個(gè)唯一值
SQL> CREATE INDEX test_idx ON test(a,b,c)
2 /
索引已創(chuàng)建。
SQL> ANALYZE TABLE test COMPUTE STATISTICS
2 FOR TABLE
3 FOR ALL INDEXES
4 FOR ALL INDEXED COLUMNS
5 /
表已分析。
SQL> set autotrace traceonly explain
SQL> SELECT * FROM test WHERE b = 99
2 /
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=24)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=4 Card=1 Bytes=24)
2 1 INDEX (SKIP SCAN) OF 'TEST_IDX' (NON-UNIQUE) (Cost=3 Card=1)
Oracle的優(yōu)化器(這里指的是CBO)能對(duì)查詢應(yīng)用Index Skip Scans至少要有幾個(gè)條件:
1 優(yōu)化器認(rèn)為是合適的.
2 索引中的前導(dǎo)列的唯一值的數(shù)量能滿足一定的條件.
3 優(yōu)化器要知道前導(dǎo)列的值分布(通過(guò)分析/統(tǒng)計(jì)表得到)
4 合適的SQL語(yǔ)句
......
更多信息請(qǐng)參考:
http://www.itpub.net/showthread.php?threadid=85948
http://www.cnoug.org/bin/ut/topic_show.cgi?id=608&h=1&bpg=1&age=100
http://www.itpub.net/showthread.php?s=&postid=985602#post985602
Oracle9i Database Performance Tuning Guide and Reference Release 2 (9.2)
Part Number A96533-02
感謝參加討論的各位高手.
