index skip scan的一些实验。

wei-xh

index skip scan的基本介绍。

Skip Scans are initiated by probing the index for distinct values of the prefix column. Each of these distinct values is then used as a starting point for a regular index search. The result is several separate searches of a single index that, when combined, eliminate the affect of the prefix column.

skip scan会探测出索引前导列的唯一值个数，每个唯一值都会作为常规扫描的入口，在此基础上做一次查找，最后合并这些查询。例如：
表employees (sex, employee_id, address) ，有一个组合索引(sex, employee_id). 在索引跳跃的情况下，我们可以逻辑上把他们看成两个索引，一个是（男，employee_id)，一个是(女，employee_id).
select * from employees where employee_id=1;
发出这个查询后，oracle先进入sex为男的入口，查找employee_id=1的条目。再进入sex为女的入口，查找employee_id=1的条目。最后合并两个结果集。

ORACLE官方说，在前导列唯一值较少的情况下，才会用到index skip can。这个其实好理解，就是入口要少。ORACLE也承认skip scan没有直接索引查询快，但可以这样说，相比于整个表扫描(table scan)，索引跳跃式扫描的速度要快得多。





看上面的这幅图。
我有个疑问，就是ORACLE是通过什么样的扫描方式找到所需要的块的，假如我现在要查找employee_id是109的记录，从图可以看出来，109的记录存在与块3和块5上。但是skip scan是通过什么样的方式定位到这两个块呢？几种可能。

可能1）先找到入口M，然后从第一个块扫起，扫到第三个块的时候发现了109，停止扫描。然后找到入口F,从块4扫起，扫描到块5的时候发现了109,由于索引已经是有序的了，后面的不用再扫了。
可能2）先找到入口M，然后把包含M的块都扫描一下，过滤出109的记录。找到入口F，然后把包含F的块都扫描一下，过滤出109的记录。这种扫描不太可能，因为跟索引完全扫描是一样的。就不是index skip scan了，ORACLE不需要神秘的加了一种skip scan，而本质上又跟索引完全扫描一样。
可能3）通过根节点和分支节点的信息，非常精准的定位到这两个块上,即块3和块5.

到底是那一种呢？

看下面的实验。
SQL> create table wxh_tbd as select * from dba_objects;
表已创建。

SQL> update wxh_tbd set object_id=1 where object_id in
  2  (select object_id from (select min(object_id) object_id ,owner from wxh_tbd group by owner));
已更新18行。
SQL> commit;
提交完成。
SQL> update wxh_tbd set object_id=100000000 where object_id in
  2   (select object_id from (select max(object_id) object_id ,owner from wxh_tbd group by owner));
已更新18行。
SQL> commit;
SQL> create index t on wxh_tbd(owner,object_id);
索引已创建。
SQL> exec dbms_stats.gather_table_stats(user,'wxh_tbd');
PL/SQL 过程已成功完成。

我的这个测试库里一共有18个schema，我们可以把索引T看成18个单独的索引。通过上面的步骤，我们做到了每个schema下面有一个最小的object_id 即1，一个最大的object_id即100000000.最小值位于每个索引的最左边，最大值位于每个索引的最右边。通过以下两个语句的逻辑读我们就可以知道，ORACLE到底是通过三种方式里的哪种来定位块了。
select /*+ index_ffs(wxh_tbd) */ count(*) from wxh_tbd where object_id=1;
select count(*) from wxh_tbd where object_id=1;
select count(*) from wxh_tbd where object_id=100000000;

实验1)看看如果是采用的index fast full scan大概需要多少逻辑读.
SQL> set autotrace trace stat
SQL> select /*+ index_ffs(wxh_tbd) */ count(*) from wxh_tbd where object_id=1;

统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        156  consistent gets

实验2）object_id为1的时候
SQL> select count(*) from wxh_tbd where object_id=1;

执行计划
----------------------------------------------------------
Plan hash value: 2915554405
-------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     5 |    19   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE  |      |     1 |     5 |            |          |
|*  2 |   INDEX SKIP SCAN| T    |     1 |     5 |    19   (0)| 00:00:01 |
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID"=1)
       filter("OBJECT_ID"=1)

统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         14  consistent gets

实验3）看看object_id为100000000的时候的逻辑读。

SQL> select count(*) from wxh_tbd where object_id=100000000;

执行计划
----------------------------------------------------------
Plan hash value: 2915554405
-------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     5 |    19   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE  |      |     1 |     5 |            |          |
|*  2 |   INDEX SKIP SCAN| T    |     1 |     5 |    19   (0)| 00:00:01 |
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID"=100000000)
       filter("OBJECT_ID"=100000000)

统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         14  consistent gets

可能1）先找到入口M，然后从第一个块扫起，扫到第三个块的时候发现了109，停止扫描。然后找到入口F,从块4扫起，扫描到块5的时候发现了109,由于索引已经是有序的了，后面的不用再扫了。
分析：如果采取这种方式，那么实验2的逻辑读应该小于实验3的逻辑读，因为如果是这样的话，实验2只需要扫描每个入口的第一个块，而实验三需要扫描每个入口的所有块。我们的实验，实验2和实验3的逻辑读是相等的。这种可能性排除

可能2）先找到入口M，然后把包含M的块都扫描一下，过滤出109的记录。找到入口F，然后把包含F的块都扫描一下，过滤出109的记录。
分析：如果采取的这种方式，那么实验2和实验3的逻辑读应该都大约等于index fast full san的逻辑读。可以是从实验结果来看，远不相等。可能性也排除。


可能3）通过根节点和分支节点的信息，非常精准的定位到这两个块上,即块3和块5.
分析：如果ORACLE采用的是上面假想的方式3扫描数据块，那么实验2和实验3的逻辑读应该相等或接近，我们的实验完全符合。


因此可以得出结论，ORACLE可以在SKIP SCAN中，选择相应的入口后，可以根据某种结构（根块？分支块？页块？）精确定位到记录的叶子块。从中找出符合条件的记录。
.
select * from employees where employee_id=1;的查询，经过index skip scan后，我在想，是不是可以‘不严谨’的这么去理解。非常类似如下的组合的效果：
select * from employees where sex='m' and employee_id=1
union
select * from employees where sex='f' and employee_id=1;
我们可以想象，如果索引前导列的唯一值很多，那么势必会大大削弱skip scan的效能，假如有50个性能，相当于要执行五十次这样的union.





还拿这个图为例，如果要查找employee_id为109的条目，ORACLE进入到入口M后，直接就可以定位到块3.而不需要扫描块1和块2.进入到入口F后，直接就可以定位到块5,而不需要扫描块4和块6.


之所以想搞明白这个，是因为最近遇到了一个这么的查询，当时非常惊讶，谓词都出现在了索引块里，怎么会用到skip scan.我们传统的一般都认为，查询的谓词里没有出现索引的前导列，才会出现index skip scan.而事实并非如此。
SQL> select count(*) from wxh_tbd where owner>'SCOTT' and object_id=5;

执行计划
----------------------------------------------------------
Plan hash value: 2915554405
-------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |    11 |    11  (10)| 00:00:01 |
|   1 |  SORT AGGREGATE  |      |     1 |    11 |            |          |
|*  2 |   INDEX SKIP SCAN| T    |     1 |    11 |    11  (10)| 00:00:01 |
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OWNER">'SCOTT' AND "OBJECT_ID"=5 AND "OWNER" IS NOT NULL)
       filter("OBJECT_ID"=5)

统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          8  consistent gets

如果理解了上面的我所论述的，我相信理解这个就不困难了。上面的查询对于index skip scan 是非常适合的。如果是index range的话，会扫描所有的首列大于'SCOTT'的索引块，从中过滤出符合条件的object_id。而index skip的话，只需要找到大于'SCOTT'的入口，然后精确的定位到符合条件的object_id，最后合并这些查询记录。









[ 本帖最后由 wei-xh 于 2010-11-28 20:25 编辑 ]

maolinxie

收藏啦

db_wang

学习了

jboracle1981

不错，收藏学习

杨奇龙

坚持不懈啊。。

我上面有人

不错！
走索引的方式有很多种，具体有哪些区别还真不清楚。希望lz能多写点关于index的文章！

sundog315

个人觉得对于index skip scan，内部应该采用并行机制，可能效果会更好。。当然，最好能够避免使用index skip scan

BHR_kramer

GOOD

yui123nbm789jhg

李成功、网盘有PS、FlashFXP、Alcohol 120%、人脸识别软件、RAR密码破解工具、系统工具、驱动、等等、教程有PS、黑客技术、网页三剑客、Word、Excel、PowerPoint 、Access等等、成功视频有、陈安之、李践、李强、刘一秒、杜云生、销售、管理应有尽有、英语、西班牙、日语、英语口语学习软件和教程、方便大家下载不断上传要什么有什么你就不用在找了、免费下载地址 http://www.ctdisk.com/u/1149889
送一句激励的话、有目标一定要去实现它、想成功就一定不要放弃、只要我不放弃就永远不会失败、人生有梦想才会更精彩

myttsd

在11.2.0.3中优化器相对于index fast full scan而言倾向于使用index skip scan，进而有时生成次优的执行计划