Oracle跳跃式索引扫描测试

Oracle 中我们知道能够使用跳跃式索引扫描（Index Skip Scan）.然而,能利用跳跃式索引扫描的情况其实是有些限制的
CREATE TABLE test AS SELECT ROWNUM a,ROWNUM-1 b ,ROWNUM-2 c,ROWNUM-3 d,ROWNUM-4 e FROM all_objects;SQL> CREATE TABLE test AS SELECT ROWNUM a,ROWNUM-1 b ,ROWNUM-2 c,ROWNUM-3 d,ROWNUM-4 e FROM all_objects;Table created.Elapsed: 00:00:02.78
SQL> desc test;
Name Null？ Type
----------------------------------------------------- -------- ------------------------------------
A NUMBER
B NUMBER
C NUMBER
D NUMBER
E NUMBERSELECT DISTINCT COUNT （a） FROM test;
SQL> SELECT DISTINCT COUNT （a） FROM test; COUNT（A）
----------
84394 CREATE INDEX test_idx ON test（a,b,c）;SQL> create index test_idx on test（a,b,c）;Index created.
ANALYZE TABLE test COMPUTE STATISTICS;
SET autotrace traceonly explain;
SELECT * FROM test WHERE b = 99;SQL> analyze table test compute statistics;Table analyzed.Elapsed: 00:00:02.46
SQL> set autotrace traceonly explain;
SQL> select * from test where b=99;
Elapsed: 00:00:00.00Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 96 （2）| 00:00:02 |
|* 1 | TABLE ACCESS FULL| TEST | 1 | 20 | 96 （2）| 00:00:02 |
--------------------------------------------------------------------------Predicate Information （identified by operation id）:
--------------------------------------------------- 1 - filter（"B"=99）

-可见这里CBO选择了全表扫描
我们接着做另一个测试
drop table test;
CREATE TABLE test AS SELECT DECODE（MOD（ROWNUM,2）, 0, "1", "2" ） a,ROWNUM-1 b,ROWNUM-2 c,ROWNUM-3 d,ROWNUM-4 e FROM all_objects
set autotrace off
select distinct a from test;
SQL> select distinct a from test;A
-
1
2Elapsed: 00:00:00.08 CREATE INDEX test_idx ON test（a,b,c）

SQL> SELECT * FROM test WHERE b = 99;
Elapsed: 00:00:00.01Execution Plan
----------------------------------------------------------
Plan hash value: 2705879578----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 4 （0）| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 17 | 4 （0）| 00:00:01 |
|* 2 | INDEX SKIP SCAN | TEST_IDX | 1 | | 3 （0）| 00:00:01 |
----------------------------------------------------------------------------------------Predicate Information （identified by operation id）:
--------------------------------------------------- 2 - access（"B"=99）
filter（"B"=99）结论：
Oracle的优化器（这里指的是CBO）能对查询应用Index Skip Scans至少要有几个条件:1 优化器认为是合适的.
2 索引中的前导列的唯一值的数量能满足一定的条件.
3 优化器要知道前导列的值分布（通过分析/统计表得到）
4 合适的SQL语句更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址