Oracle跳跃式索引扫描测试

179次阅读

共计 2845 个字符，预计需要花费 8 分钟才能阅读完成。

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

更多 Oracle 相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12

本文永久更新链接地址：http://www.linuxidc.com/Linux/2016-08/134057.htm

正文完

星哥玩云-微信公众号