oracle松散索引扫描,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.78SQL> 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)----------     84394CREATE 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.46SQL> 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-12Elapsed: 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语句