index skip scan的一些实验

转载 2012年03月27日 23:05:19

http://space.itpub.net/22034023/viewspace-680475
index 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。这个其实好理解,就是入口要少。


事实上.jpg


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


1)先找到入口M,然后从第一个块扫起,扫到第三个块的时候发现了109,停止扫描。然后找到入口F,从块4扫起,扫描到块5的时候发现了109,由于索引已经是有序的了,后面的不用再扫了。
2)先找到入口M,然后把包含M的块都扫描一下,过滤出109的记录。找到入口F,然后把包含F的块都扫描一下,过滤出109的记录
3)通过根节点和分支节点的信息,非常精准的一下子定位到这两个块上。
到底是那一种呢?

看下面的实验
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);
索引已创建。

我的这个测试库里一共有18个schema,通过上面的步骤,我们做到了每个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 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


如果ORACLE采用的是上面假想的方式1扫描数据块,那么实验2的逻辑读应该远小于实验3的逻辑读。实验2和实验3的逻辑读是相等的。这种可能性排除
如果ORACLE采用的是上面假想的方式2扫描数据块,那么实验2和实验3的逻辑读应该都大约等于index fast full san的逻辑读。可以是从实验结果来看,远不相等。
如果ORACLE采用的是上面假想的方式3扫描数据块,那么实验2和实验3的逻辑读应该相等或接近,我们的实验完全符合。


因此可以得出结论,ORACLE可以在SKIP SCAN中,选择相应的入口后,可以根据某种结构(根块?分支块?页块?)精确定位到记录的叶子块。从中找出符合条件的记录。


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


之所以想搞明白这个,是因为最近遇到了一个这么的查询,当时非常惊讶,谓词都出现在了索引块里,怎么会用到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即可。

Oracle:跳跃式索引(Skip Scan Index)浅析

在Oracle9i中,有一个新的特性:跳跃式索引(Skip Scan Index)。当表有一个复合索引,而在查询中有除了索引中第一列的其他列作为条件,并且优化器模式为CBO,这时候查询计划就有可能使用...
  • zuixinnet
  • zuixinnet
  • 2013年04月13日 21:08
  • 674

复合索引和INDEX SKIP SCAN

今天是2014-01-21,在此学习一下复合索引和INDEX SKIP SCAN; 复合索引很简单无非就是在创建索引的时候指定接字段,但是要注意字段的选择是有一定的可参考性的,在字段选择的时候我们一般...
  • xiaohai20102010
  • xiaohai20102010
  • 2014年01月21日 20:17
  • 1214

Oracle:跳跃式索引(Skip Scan Index)浅析

在Oracle9i中,有一个新的特性:跳跃式索引(Skip Scan Index)。当表有一个复合索引,而在查询中有除了索引中第一列的其他列作为条件,并且优化器模式为CBO,这时候查询计划就有可能使用...
  • shaochenshuo
  • shaochenshuo
  • 2016年06月30日 17:45
  • 256

索引扫描还是全表扫描(Index Scan Or Full Table Scan)

作者:Sky.Jian | 可以任意转载, 但转载时务必以超链接形式标明文章原始出处 和 作者信息 及 版权声明  链接:http://isky000.com/database/index_sc...
  • xpzhang123
  • xpzhang123
  • 2015年12月04日 11:26
  • 1047

index range scan 单块读 无法走并行

SQL> create table t1 as select * from dba_objects; SQL> create index i1_idx on t1(owner); BEGIN ...
  • zhaoyangjian724
  • zhaoyangjian724
  • 2014年11月05日 14:27
  • 457

Go语言:解决数据库中null值的问题

Go语言:解决数据库中null值的问题 本文主要介绍如何使用go语言database/sql库从数据库中读取null值的问题,以及如何向数据库中插入null值。本文在这里使用的是sql.NullS...
  • qq_15437667
  • qq_15437667
  • 2017年12月12日 12:50
  • 338

极深网络(ResNet/DenseNet): Skip Connection为何有效及其它

转自:http://blog.csdn.net/malefactor/article/details/67637785 感谢张俊林通俗的分析。 ========================...
  • langb2014
  • langb2014
  • 2017年03月29日 09:52
  • 2559

STM32-按键输入实验学习笔记

按键扫描函数key.c片段://按键初始化函数 //特别注意:在该函数之后,JTAG将无法使用(SWD也无法使用) //如果想JTAG仿真,可以屏蔽该函数。 //PA0.13.15 设置成输入 voi...
  • zzwdkxx
  • zzwdkxx
  • 2013年02月28日 16:08
  • 5306

index range scan(索引范围扫描)的计划分析

 一、index range scan(索引范围扫描): 1、对于unique index(唯一性索引,是带唯一性约束的,普通索引则没有唯一性约束。scott.emp表是主键,带有唯一性约束,...
  • HelloCqk1
  • HelloCqk1
  • 2016年04月15日 16:17
  • 393

Mongodb从0到1系列四: Limit与Skip方法、排序、索引

Mongodb从0到1系列一:下载、安装、启动、停止 Mongodb从0到1系列二:数据库与集合操作文档、增删改查 Mongodb从0到1系列三: 条件查询、大小写 7. Limit与Skip方...
  • qingsong3333
  • qingsong3333
  • 2017年07月27日 23:34
  • 236
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:index skip scan的一些实验
举报原因:
原因补充:

(最多只允许输入30个字)