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.78

SQL> desc test;

Name                              Null?    Type

----------------------------------------------------- -------- ------------------------------------

A                                NUMBER

B                                NUMBER

C                                NUMBER

D                                NUMBER

E                                NUMBER

SELECT 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.00

Execution 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

2

Elapsed: 00:00:00.08

CREATE INDEX test_idx ON test(a,b,c)

SQL> SELECT *  FROM test WHERE b = 99;

Elapsed: 00:00:00.01

Execution 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语句