跳跃式索引扫描的结构或算法特征

2002年的时候有一天看到 oracle 支持索引的skip  scan ,也就是说

假设存在  index (a,b)

则查询 select * from table where b = ?  将能用到索引

从索引的结构想来，当时有些迷惑，对此进行过思考从而写下此文

 

跳跃式索引扫描的结构猜想

 

首先oracle 肯定不会是使用多个索引来掩盖问题的本质，那现在请允许我

假设索引(a,b,c)
我猜测是索引b或者c也有一个相临值之间的 “地址指针”
但他们之间是否具有树型结构不清楚
若不具有树结构则每次从该a 不同值进入从第一个b 值开始寻找符合条件数据
若具有树结构则每次进入一个a然后就迅速定位到符合条件b值
这样可能对每一个a值都要进入一次

我这样猜测
那么就可以通过控制a 或者 b值的数据分布和符合条件比例来逐步证明自己的猜测是否正确

举个例子
假设数据分布:
a b c
1 1 1
1 2 2
1 2 3
1 3 1
2 1 1
2 2 2
2 2 3

当查询 b = 2 的时候
不存在树结构： 先进入a=1, 然后顺序((也可2分法等方法,因为是排序好的)
)b=1,b=2,发现2条，不用比较3了，因为是有序的
存在树结构： 进入a= 1 ,迅速定位到2，然后找出和服条件记录

然后进入a=2 重复上面的过程

当查询 c = 3 的时候，先进入 a = 1,b=1,发现没有
进入 a=1,b=2,无树结构能快速定位到3(也可顺序可2分法,因为是排序好的),如此重复上面的步骤


由这个猜想
当数据量特别大并且数据可选择性很大的时候这种方式具有很大的优点
但这种猜测方式，假如 a 不具有相同值，而b 或者c 选择性比较小，那么这种结构的跳跃式扫描将是一种灾难

所以，oracle将根据统计分析结构来决定

但是，假如上述猜测不成立！
那我们可以从算法的角度来考虑

也就是说结构本身没有发生变化，但是在索引的搜索算法上可以从多个root ,branch进入进行扫描数据，这样当索引层次多的时候代价获取会高一些，但是不用维护结构的变化。

 

 

几个月后做了次测试

 

存在index(a,b)
当a的选择性不高的时候，oracle进行 跳跃式扫描的 优点明显
当a的选择性高的时候，跳跃式扫描失去了意义
既然有跳跃一说，就是说每次扫描的时候，会有一个入口进去，扫描，然后从新的入口进去，扫描……如此反复

这个入口，就是 a 的不同的值



SQL> create table t as select * from all_objects ;

表已创建。

SQL> create index t_index1 on t(owner,object_name);

索引已创建。
SQL> analyze table t compute statistics;

表已分析。

SQL> set autotrace on
SQL> select DATA_OBJECT_ID,created from t where object_name = 'DBA_EXTENTS';

DATA_OBJECT_ID CREATED
-------------- ----------
12-5月 -02
12-5月 -02

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=35 Card=2 Bytes=64)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=35 Card=2 Bytes
=64)

2 1 INDEX (SKIP SCAN) OF 'T_INDEX1' (NON-UNIQUE) (Cost=34 Ca
rd=1)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
26 consistent gets
0 physical reads
0 redo size
467 bytes sent via SQL*Net to client
425 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed

SQL> drop index t_index1;

索引已丢弃。

SQL> create index t_index1 on t (object_name,owner);

索引已创建。

SQL> analyze table t compute statistics;

表已分析。

SQL> select * from t where owner = 'TEST';

OWNER OBJECT_NAME
------------------------------ ------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------- ------------------
CREATED LAST_DDL_T TIMESTAMP STATUS T G S
---------- ---------- ------------------- ------- - - -
TEST A
30882 30882 TABLE
06-12月-02 06-12月-02 2002-12-06:17:35:38 VALID N N N

TEST B
33237 33237 TABLE
11-2月 -03 11-2月 -03 2003-02-11:10:07:49 VALID N N N

OWNER OBJECT_NAME
------------------------------ ------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------- ------------------
CREATED LAST_DDL_T TIMESTAMP STATUS T G S
---------- ---------- ------------------- ------- - - -

TEST DBEXPERT_PLAN1
30333 30333 TABLE
02-12月-02 02-12月-02 2002-12-02:17:26:29 VALID N N N

TEST LMT
33468 33468 TABLE

OWNER OBJECT_NAME
------------------------------ ------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------- ------------------
CREATED LAST_DDL_T TIMESTAMP STATUS T G S
---------- ---------- ------------------- ------- - - -
27-2月 -03 27-2月 -03 2003-02-27:09:22:18 VALID N N N

TEST LOGON_HISTORY
31284 TRIGGER
30-12月-02 30-12月-02 2002-12-30:19:02:08 VALID N N N

TEST MANLMT

OWNER OBJECT_NAME
------------------------------ ------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------- ------------------
CREATED LAST_DDL_T TIMESTAMP STATUS T G S
---------- ---------- ------------------- ------- - - -
33469 33469 TABLE
27-2月 -03 27-2月 -03 2003-02-27:10:07:10 VALID N N N

TEST PLAN_TABLE
33482 33482 TABLE
03-3月 -03 03-3月 -03 2003-03-03:10:49:09 VALID N N N

OWNER OBJECT_NAME
------------------------------ ------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------- ------------------
CREATED LAST_DDL_T TIMESTAMP STATUS T G S
---------- ---------- ------------------- ------- - - -
TEST SESSION_HISTORY
31287 31287 TABLE
30-12月-02 30-12月-02 2002-12-30:19:00:41 VALID N N N

TEST T
33485 33485 TABLE
03-3月 -03 03-3月 -03 2003-03-03:17:12:42 VALID N N N

OWNER OBJECT_NAME
------------------------------ ------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------- ------------------
CREATED LAST_DDL_T TIMESTAMP STATUS T G S
---------- ---------- ------------------- ------- - - -

TEST TEST
33483 33483 TABLE
03-3月 -03 03-3月 -03 2003-03-03:10:52:08 VALID N N N

TEST TEST_INDEX
33484 33484 INDEX

OWNER OBJECT_NAME
------------------------------ ------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------- ------------------
CREATED LAST_DDL_T TIMESTAMP STATUS T G S
---------- ---------- ------------------- ------- - - -
03-3月 -03 03-3月 -03 2003-03-03:10:52:34 VALID N N N

已选择11行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=43 Card=927 Bytes=79
722)

1 0 TABLE ACCESS (FULL) OF 'T' (Cost=43 Card=927 Bytes=79722)

Statistics
----------------------------------------------------------
71 recursive calls
0 db block gets
450 consistent gets
0 physical reads
0 redo size
2264 bytes sent via SQL*Net to client
425 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
11 rows processed

 

感谢 chao_ping 当时给出的一个例子

 

SELECT OWNER,OBJECT_ID FROM YAFENG WHERE object_name='DBA_TABLES'
16:44:01 scott@ORA9> /

OWNER OBJECT_ID
------------------------------------------------------------ ----------
PUBLIC 1813
SYS 1812

Elapsed: 00:00:00.15

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=11 Card=1 Bytes=22)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'YAFENG' (Cost=11 Card=1 Bytes=22)
2 1 INDEX (SKIP SCAN) OF 'IDX_YAFENG' (NON-UNIQUE) (Cost=10 Card=1)

12 consistent gets
16:44:02 scott@ORA9> select distinct owner from yafeng;

OWNER
------------------------------------------------------------
BIDDER
EACHPAY
OUTLN
PERFSTAT
PUBLIC
SCOTT
SYS
SYSTEM

8 rows selected.
12 consistent gets:

root block -> branch block(begin with bidder:no object_name like DBA_tables) ---one block gets
-> Branch block(begin with eachpay:no object_name like dba_tables) ---one block gets
-> Branch block(begin with outln:no object_name like dba_tables) --one block gets
-> Branch block(begin with perfstat:no object_name like dba_tables) ---one block gets
-> Branch block(begin with public:find one object named dba_tabes)->rowid-row (2 block gets)
-> Branch block(begin with scott:no object_name like dba_tables) --one block gets
-> Branch block(begin with system:no object_name like dba_tables) --one block gets
-> Branch block(begin with sys:no object_name like dba_tables) ->rowid-row(2 block gets)