关于Oracle 9i 跳跃式索引扫描(Index Skip Scan)的小测试

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.

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

SQL>
SQL> CREATE INDEX test_idx ON test(a,b,c)
2  /

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)

--可见这里CBO选择了全表扫描.

--我们接着做另一个测试:

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  /

SQL> set autotrace off
SQL> select distinct a from test;

A
--
1
2

--A列只有两个唯一值

SQL> CREATE INDEX test_idx ON test(a,b,c)
2  /

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的优化器(这里指的是CBO)能对查询应用Index Skip Scans至少要有几个条件:

1 优化器认为是合适的.
2 索引中的前导列的唯一值的数量能满足一定的条件.
3 优化器要知道前导列的值分布(通过分析/统计表得到)
4 合适的SQL语句
......