跳跃式索引(Skip Scan Index)的浅析(作者:fuyuncat)
来源:互联网 发布:mac ai如何剪切图片 编辑:程序博客网 时间:2024/04/30 02:13
在Oracle9i中,有一个新的特性:跳跃式索引(Skip Scan Index)。当表有一个复合索引,而在查询中有除了索引中第一列的其他列作为条件,并且优化器模式为CBO,这时候查询计划就有可能使用到SS。此外,还可以通过使用提示index_ss(CBO下)来强制使用SS。
举例:
SQL> create table test1 (a number, b char(10), c varchar2(10));
Table created.
SQL> create index test_idx1 on test1(a, b);
Index created.
SQL> set autotrace on
SQL> select /*+index_ss(test1 test_idx1)*/* from test1 a
2 where b ='a';
no rows selected
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=32)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST1' (Cost=2 Card=1 Bytes=32)
2 1 INDEX (SKIP SCAN) OF 'TEST_IDX1' (NON-UNIQUE)
但并不是任何情况下都会使用到SS。在Oracle的官方文档中,除了提到需要CBO,并且对表进行过分析外,还需要保证第一列的distinct value非常小。这一段是从官方文档上摘取的关于SS的一段解释:
Index skip scans improve index scans by nonprefix columns since it is often faster to scan index blocks than scanning table data blocks.
In this case a composite index is split logically into smaller subindexes. The number of logical subindexes depends on the cardinality of the initial column. Hence it is now possible to use the index even if the leading column is not used in a where clause.
Oracle并没有公布过关于SS更多的内部技术细节。但注意上面的这句话:In this case a composite index is split logically into smaller subindexes. The number of logical subindexes depends on the cardinality of the initial column.即Oralce会对复合索引进行逻辑划分,分成多个子索引。可以这样理解,Oracle将索引从逻辑上划分为a.num_distinct个子索引,每次对一个子索引进行扫描。因此SS的索引扫描成本为a.num_distinct.
下面做一些试验,看看在什么情况下Oracle采用SS.
首先要保证使用SS的几个必要条件:
· Optimizer为CBO
· 相关表要有正确的统计数据
· Oracle DB版本为9i以上
下面就是一个使用到SS的特殊条件:第一列的distinct num要足够小。小到什么程度呢?
还是以上面的表为例(省略中间的麻烦步骤,取两个临界值做实验):
取第一列distinct number为37:
SQL> truncate table test1;
Table truncated.
SQL> begin
2 for i in 1..100000 loop
3 insert into test1 values (mod(i,37), to_char(i), to_char(i));
4 end loop;p;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> analyze table test1 compute statistics;
Table analyzed.
SQL> set autotrace on explain
SQL> select * from test1
2 where b = '500';
A B C
---------- ---------- ----------
19 500 500
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=37 Card=1 Bytes=17)
1 0 TABLE ACCESS (FULL) OF 'TEST1' (Cost=37 Card=1 Bytes=17)
再取第一列distinct number为36:
SQL> truncate table test1;
Table truncated.
SQL> begin
2 for i in 1..100000 loop
3 insert into test1 values (mod(i,36), to_char(i), to_char(i));
4 end loop;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> analyze table test1 compute statistics;
Table analyzed.
SQL> select * from test1 where b = '500';
A B C
---------- ---------- ----------
32 500 500
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=12 Card=1 Bytes=17)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST1' (Cost=12 Card=1 B
ytes=17)
2 1 INDEX (SKIP SCAN) OF 'TEST_IDX1' (NON-UNIQUE) (Cost=37 C
ard=1)
从上面试验结果看,FTS的cost是37。当第一列distinct number小于这个值时,Oracle选择了SS。
继续试验:
SQL> select count(*) from test1
2 where b <= '1';
COUNT(*)
----------
1
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=12 Card=1 Bytes=10)
1 0 SORT (AGGREGATE)
2 1 INDEX (SKIP SCAN) OF 'TEST_IDX1' (NON-UNIQUE) (Cost=37 C
ard=1 Bytes=10)
注意:在b中’10’是比’1’大的最小值(char(10)类型)
SQL> select count(*) from test1
2 where b <= '10';
COUNT(*)
----------
2
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=37 Card=1 Bytes=10)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'TEST1' (Cost=37 Card=773 Bytes=7
730)
观察结果,这时候影响的因素是cardinality了。第二个查询计划中的cardinality值(773)正是b<=’10’的cardinality值:
SQL> set autotrace off
SQL> select 100000*(to_number('31302020202020202020', 'xxxxxxxxxxxxxxxxxxxx')-to
_number('31202020202020202020', 'xxxxxxxxxxxxxxxxxxxx'))/(to_number('39393939392
020202020', 'xxxxxxxxxxxxxxxxxxxx')-to_number('31202020202020202020', 'xxxxxxxxx
xxxxxxxxxxx'))+1 from dual;
100000*(TO_NUMBER('31302020202020202020','XXXXXXXXXXXXXXXXXXXX')-TO_NUMBER('3120
--------------------------------------------------------------------------------
772.791768
再看一个含有第一列条件的等效的语句:
SQL> set autotrace on explain
SQL> select count(*) from test1
2 where a>=0
3 and b <='1';
COUNT(*)
----------
1
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=12 Card=1 Bytes=12)
1 0 SORT (AGGREGATE)
2 1 INDEX (SKIP SCAN) OF 'TEST_IDX1' (NON-UNIQUE) (Cost=37 C
ard=1 Bytes=12)
再做几个有趣的试验,下面的试验条件是不满足SS的,但是请注意查询返回列队查询计划的影响:
SQL> truncate table test1;
Table truncated.
SQL> begin
2 for i in 1..100000 loop
3 insert into test1 values (i, to_char(i), to_char(i));
4 end loop;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> analyze table test1 compute statistics;
Table analyzed.
SQL> select * from test1
2 where b = '500';
A B C
---------- ---------- ----------
500 500 500
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=37 Card=1 Bytes=19)
1 0 TABLE ACCESS (FULL) OF 'TEST1' (Cost=37 Card=1 Bytes=19)
改变返回列:
SQL> select count(*) from test1
2 where b = '500';
COUNT(*)
----------
1
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=34 Card=1 Bytes=10)
1 0 SORT (AGGREGATE)
2 1 INDEX (FAST FULL SCAN) OF 'TEST_IDX1' (NON-UNIQUE) (Cost
=34 Card=1 Bytes=10)
再改变一种:
SQL> select a from test1
2 where b = '500';
A
----------
500
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=34 Card=1 Bytes=14)
1 0 INDEX (FAST FULL SCAN) OF 'TEST_IDX1' (NON-UNIQUE) (Cost=3
4 Card=1 Bytes=14)
使用RBO呢?
SQL> select /*+rule*/a from test1
2 where b = '500';
A
----------
500
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: RULE
1 0 TABLE ACCESS (FULL) OF 'TEST1'
值得一提的是,上述任何一个例子在8i中执行的话,都不会使用到索引(无论是否符合SS的条件)。
- 跳跃式索引(Skip Scan Index)的浅析(作者:fuyuncat)
- Oracle:跳跃式索引(Skip Scan Index)浅析
- Oracle:跳跃式索引(Skip Scan Index)浅析
- 解析:索引跳跃式扫描 (index skip scan)
- 关于Oracle 9i 跳跃式索引扫描(Index Skip Scan)的小测试
- 复合索引和INDEX SKIP SCAN
- index skip scan的一些实验。
- index skip scan的一些实验
- 什么是INDEX SKIP SCAN
- 理解index skip scan
- 关于INDEX SKIP SCAN
- index skip scan
- index skip scan
- index range scan,index fast full scan,index skip scan发生的条件
- index range scan,index fast full scan,index skip scan发生的条件
- index range scan,index fast full scan,index skip scan发生的条件
- index range scan,index fast full scan,index skip scan发生的条件
- 深入理解Oracle索引(1):INDEX SKIP SCAN 和 INDEX RANGE SCAN
- 从辞职说起
- DMA使用说明
- 浅谈vector、deque与list
- 数据库一致关闭后REDO LOG全部丢失的解决办法
- 转移统计信息
- 跳跃式索引(Skip Scan Index)的浅析(作者:fuyuncat)
- 走进自己的内心
- 关于Bind Variables Peeking
- View statistics in the data dictionary
- 关于同操作系统的32bit到64bit的数据库迁移参考文档
- rebuild和rebuild online的区别
- Some hint
- 一条SQL语句如何被解析
- 关于CURSOR_SHARING三个取值的区别