| 常见索引扫描方式（四）：INDEX SKIP SCAN

常见索引扫描方式（四）：INDEX SKIP SCAN

作者：OoNiceDream【转载时请务必以超链接形式标明文章原始出处和作者信息】
链接：http://www.dbaroad.me/archives/2009/06/index_skip_scan.html

索引跳跃式扫描（INDEX SKIP SCAN），需要在CBO模式下才能起作用，当查询谓词中不带有前导列，且前导列唯一值较少时，才有可能用上该索引扫描方式。下面来看看INDEX SKIP SCAN 是如何扫描的：

准备测试数据：

SQL> create table test(x number,y number);
 
Table created.
 
SQL> insert into test
  2  select decode(mod(rownum,2),0,1,2),rownum
  3  from dba_objects where rownum&lt;10000;
 
9999 rows created.
 
SQL> commit;
 
Commit complete.
 
SQL> create index ind_xy on test(x,y);
 
Index created.
 
SQL> exec dbms_stats.gather_table_stats('SYS','TEST');
 
PL/SQL procedure successfully completed.
 
SQL> set autot trace exp
SQL> select * from test where y=5;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 854635220
 
---------------------------------------------------------------------------
| Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |     1 |     6 |     3   (0)| 00:00:01 |
|*  1 |  INDEX SKIP SCAN | IND_XY |     1 |     6 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("Y"=5)
       filter("Y"=5)
 
SQL> select * from test where y between 1000 and 1200;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 217508114
 
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   202 |  1212 |     5   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |   202 |  1212 |     5   (0)| 00:00:01 |
--------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("Y"< =1200 AND "Y">=1000)
 
SQL> set autot off

为了让类似于“y between 1000 and 1200”的范围查询也走索引，我们修改一下统计信息：

SQL> exec dbms_stats.set_table_stats('SYS','TEST',numrows=>1000000,numblks=>3000000);
 
PL/SQL procedure successfully completed.
 
SQL> exec dbms_stats.set_index_stats('SYS','IND_XY',NUMROWS=>1000000,NUMLBLKS=>3000000);
 
PL/SQL procedure successfully completed.
 
SQL> set autot trace exp
SQL> select * from test where y between 1000 and 2500;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 854635220
 
---------------------------------------------------------------------------
| Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |   150K|   880K|   407K  (1)| 10:27:02 |
|*  1 |  INDEX SKIP SCAN | IND_XY |   150K|   880K|   407K  (1)| 10:27:02 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("Y">=1000 AND "Y"< =2500)
       filter("Y"<=2500 AND "Y">=1000)

先设置10046事件，看看读取的是哪些BLOCK：

SQL> alter system flush buffer_cache;
 
System altered.
 
SQL> oradebug setmypid
Statement processed.
SQL> oradebug event 10046 trace name context forever,level 12;
Statement processed.
SQL> select * from test where y=1000;
 
         X          Y
---------- ----------
         1       1000
 
PARSING IN CURSOR #3 len=31 dep=0 uid=0 oct=3 lid=0 tim=28576548594261 hv=3600506749 ad='3c10ae90'
select * from test where y=1000
END OF STMT
PARSE #3:c=10000,e=9280,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=28576548594242
BINDS #3:
EXEC #3:c=0,e=152,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=28576548594524
WAIT #3: nam='SQL*Net message to client' ela= 4 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=28576548594595
WAIT #3: nam='db file sequential read' ela= 74 file#=1 block#=69522 blocks=1 obj#=109868 tim=28576548594838
WAIT #3: nam='db file sequential read' ela= 106 file#=1 block#=69523 blocks=1 obj#=109868 tim=28576548595032
WAIT #3: nam='db file sequential read' ela= 18 file#=1 block#=69524 blocks=1 obj#=109868 tim=28576548595141
FETCH #3:c=0,e=533,p=3,cr=3,cu=0,mis=0,r=1,dep=0,og=1,tim=28576548595199
WAIT #3: nam='SQL*Net message from client' ela= 515 driver id=1650815232 #bytes=1 p3=0 obj#=109868 tim=28576548595798
WAIT #3: nam='db file sequential read' ela= 19 file#=1 block#=69535 blocks=1 obj#=109868 tim=28576548595905
WAIT #3: nam='db file sequential read' ela= 50 file#=1 block#=69536 blocks=1 obj#=109868 tim=28576548596016
WAIT #3: nam='db file sequential read' ela= 18 file#=1 block#=69548 blocks=1 obj#=109868 tim=28576548596098
FETCH #3:c=0,e=294,p=3,cr=5,cu=0,mis=0,r=0,dep=0,og=1,tim=28576548596129
WAIT #3: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=109868 tim=28576548596199
WAIT #3: nam='SQL*Net message from client' ela= 500 driver id=1650815232 #bytes=1 p3=0 obj#=109868 tim=28576548596726
STAT #3 id=1 cnt=1 pid=0 pos=1 obj=109868 op='INDEX SKIP SCAN IND_XY (cr=8 pr=6 pw=0 time=517 us)'

可以看到Block读取的顺序为：69522->69523->69524->69535->69536->69548

再来看看“y between 1000 and 2500”的BLOCK读取顺序：

select * from test where y between 1000 and 2500
WAIT #3: nam='db file sequential read' ela= 966 file#=1 block#=69522 blocks=1 obj#=109868 tim=28579403261397
WAIT #3: nam='db file sequential read' ela= 101 file#=1 block#=69523 blocks=1 obj#=109868 tim=28579403261570
WAIT #3: nam='db file sequential read' ela= 17 file#=1 block#=69524 blocks=1 obj#=109868 tim=28579403261677
WAIT #3: nam='db file sequential read' ela= 19 file#=1 block#=69525 blocks=1 obj#=109868 tim=28579403274372
WAIT #3: nam='db file sequential read' ela= 19 file#=1 block#=69526 blocks=1 obj#=109868 tim=28579403291497
WAIT #3: nam='db file sequential read' ela= 19 file#=1 block#=69535 blocks=1 obj#=109868 tim=28579403294076
WAIT #3: nam='db file sequential read' ela= 51 file#=1 block#=69536 blocks=1 obj#=109868 tim=28579403294187
WAIT #3: nam='db file sequential read' ela= 18 file#=1 block#=69537 blocks=1 obj#=109868 tim=28579403297464
WAIT #3: nam='db file sequential read' ela= 19 file#=1 block#=69538 blocks=1 obj#=109868 tim=28579403313975
WAIT #3: nam='db file sequential read' ela= 20 file#=1 block#=69548 blocks=1 obj#=109868 tim=28579403325960

为69522->69523->69524->69525->69526->69535->69536->69537->69538->69548

我们先将几个重要的BLOCK DUMP出来，再来分析下INDEX SKIP SCAN为何是这样读取的。
查询首先读取的分支节点69522：

Branch block dump
=================
header address 9223372041152863300=0x80000001002f9c44
kdxcolev 1
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 3
kdxcosdc 0
kdxconro 25
kdxcofbo 78=0x4e
kdxcofeo 7741=0x1e3d
kdxcoavs 7663
kdxbrlmc 4263827=0x410f93
kdxbrsno 0
kdxbrbksz 8040 
kdxbr2urrc 0
row#0[8028] dba: 4263828=0x410f94 ##BLOCK：69524
col 0; len 2; (2):  c1 02
col 1; len 3; (3):  c2 09 05      对应Y值804
col 2; TERM
row#1[8017] dba: 4263829=0x410f95 ##BLOCK：69525
col 0; len 2; (2):  c1 02
col 1; len 2; (2):  c2 11         对应Y值1600
col 2; TERM
row#2[8005] dba: 4263830=0x410f96 ##BLOCK：69526
col 0; len 2; (2):  c1 02
col 1; len 3; (3):  c2 18 61      对应Y值2396
col 2; TERM
row#3[7993] dba: 4263831=0x410f97 ##BLOCK：69527
col 0; len 2; (2):  c1 02
col 1; len 3; (3):  c2 20 5d      对应Y值3192
col 2; TERM
row#4[7981] dba: 4263832=0x410f98
col 0; len 2; (2):  c1 02
col 1; len 3; (3):  c2 28 59
col 2; TERM
row#5[7969] dba: 4263833=0x410f99
col 0; len 2; (2):  c1 02
col 1; len 3; (3):  c2 30 55
col 2; TERM
row#6[7957] dba: 4263834=0x410f9a
col 0; len 2; (2):  c1 02
col 1; len 3; (3):  c2 38 51
col 2; TERM
row#7[7945] dba: 4263835=0x410f9b
col 0; len 2; (2):  c1 02
col 1; len 3; (3):  c2 40 4d
col 2; TERM
row#8[7933] dba: 4263836=0x410f9c
col 0; len 2; (2):  c1 02
col 1; len 3; (3):  c2 48 49
col 2; TERM
row#9[7921] dba: 4263837=0x410f9d
col 0; len 2; (2):  c1 02
col 1; len 3; (3):  c2 50 45
col 2; TERM
row#10[7909] dba: 4263838=0x410f9e
col 0; len 2; (2):  c1 02
col 1; len 3; (3):  c2 58 41
col 2; TERM
row#11[7897] dba: 4263839=0x410f9f ##BLOCK：69535
col 0; len 2; (2):  c1 02
col 1; len 3; (3):  c2 60 3d      对应Y值9560
col 2; TERM
row#12[7885] dba: 4263840=0x410fa0 ##BLOCK：69536
col 0; len 2; (2):  c1 03
col 1; len 3; (3):  c2 04 40      对应Y值363
col 2; TERM
row#13[7873] dba: 4263841=0x410fa1 ##BLOCK：69537
col 0; len 2; (2):  c1 03
col 1; len 3; (3):  c2 0c 3c      对应Y值1159
col 2; TERM
row#14[7861] dba: 4263842=0x410fa2 ##BLOCK：69538
col 0; len 2; (2):  c1 03
col 1; len 3; (3):  c2 14 38      对应Y值1955
col 2; TERM
row#15[7849] dba: 4263843=0x410fa3 ##BLOCK：69539
col 0; len 2; (2):  c1 03
col 1; len 3; (3):  c2 1c 34       对应Y值2751
col 2; TERM
row#16[7837] dba: 4263844=0x410fa4
col 0; len 2; (2):  c1 03
col 1; len 3; (3):  c2 24 30
col 2; TERM
row#17[7825] dba: 4263845=0x410fa5
col 0; len 2; (2):  c1 03
col 1; len 3; (3):  c2 2c 2c
col 2; TERM
row#18[7813] dba: 4263846=0x410fa6
col 0; len 2; (2):  c1 03
col 1; len 3; (3):  c2 34 28
col 2; TERM
row#19[7801] dba: 4263847=0x410fa7
col 0; len 2; (2):  c1 03
col 1; len 3; (3):  c2 3c 24
col 2; TERM
row#20[7789] dba: 4263848=0x410fa8
col 0; len 2; (2):  c1 03
col 1; len 3; (3):  c2 44 20
col 2; TERM
row#21[7777] dba: 4263849=0x410fa9
col 0; len 2; (2):  c1 03
col 1; len 3; (3):  c2 4c 1c
col 2; TERM
row#22[7765] dba: 4263850=0x410faa
col 0; len 2; (2):  c1 03
col 1; len 3; (3):  c2 54 18
col 2; TERM
row#23[7753] dba: 4263851=0x410fab
col 0; len 2; (2):  c1 03
col 1; len 3; (3):  c2 5c 14
col 2; TERM
row#24[7741] dba: 4263852=0x410fac ##对应BLOCK：69548
col 0; len 2; (2):  c1 03
col 1; len 3; (3):  c2 64 10       对应Y值9915
col 2; TERM
----- end of branch block dump -----

再DUMP出BLOCK：69535，可以看出该BLOCK有X值为1的索引条目，也有X值为2的索引条目，我们可以这样认为，该BLOCK是索引中X值为1的最左边，X值为2的最右边：

Leaf block dump
===============
header address 9223372041152863324=0x80000001002f9c5c
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 3
kdxcosdc 0
kdxconro 401
kdxcofbo 838=0x346
kdxcofeo 1654=0x676
kdxcoavs 816
kdxlespl 0
kdxlende 0
kdxlenxt 4263840=0x410fa0
kdxleprv 4263838=0x410f9e
kdxledsz 0
kdxlebksz 8016
row#0[8000] flag: ------, lock: 0, len=16
col 0; len 2; (2):  c1 02
col 1; len 3; (3):  c2 60 3d
col 2; len 6; (6):  00 41 0f 89 01 cd
row#1[7984] flag: ------, lock: 0, len=16
col 0; len 2; (2):  c1 02
col 1; len 3; (3):  c2 60 3f
col 2; len 6; (6):  00 41 0f 89 01 cf
..........................................
.................省略部份.................
..........................................
row#217[4532] flag: ------, lock: 0, len=16
col 0; len 2; (2):  c1 02
col 1; len 3; (3):  c2 64 5f
col 2; len 6; (6):  00 41 0f 8a 01 21
row#218[4516] flag: ------, lock: 0, len=16
col 0; len 2; (2):  c1 02
col 1; len 3; (3):  c2 64 61
col 2; len 6; (6):  00 41 0f 8a 01 23
row#219[4500] flag: ------, lock: 0, len=16
col 0; len 2; (2):  c1 02                  X：1
col 1; len 3; (3):  c2 64 63               Y：9998（X为1时，Y的最大值）
col 2; len 6; (6):  00 41 0f 8a 01 25
row#220[4485] flag: ------, lock: 0, len=15
col 0; len 2; (2):  c1 03                  X：2
col 1; len 2; (2):  c1 02                  Y：1 （X为2时，Y的最小值）
col 2; len 6; (6):  00 41 0e da 00 00
row#221[4470] flag: ------, lock: 0, len=15
col 0; len 2; (2):  c1 03
col 1; len 2; (2):  c1 04
col 2; len 6; (6):  00 41 0e da 00 02
row#222[4455] flag: ------, lock: 0, len=15
col 0; len 2; (2):  c1 03
col 1; len 2; (2):  c1 06
col 2; len 6; (6):  00 41 0e da 00 04
..........................................
.................省略部份.................
..........................................
row#400[1654] flag: ------, lock: 0, len=16
col 0; len 2; (2):  c1 03
col 1; len 3; (3):  c2 04 3e
col 2; len 6; (6):  00 41 0e da 01 68
----- end of leaf block dump -----
 
SQL> select max(y) from test where x=1;
 
    MAX(Y)
----------
      9998
SQL> select dump(9998,16) from dual;    
 
DUMP(9998,16)
---------------------
Typ=2 Len=3: c2,64,63

从上面这些trace中可以看出，查询读取了Branch block后，便分别在X值为1，及X值为2的两个范围内的leaf block中进行扫描。例如查询，y=1000,读取顺序为：

69522->69523->69524->69535->69536->69548：
 
69522 Branch block
 
69523 X值为1的leaf block的起始BLOCK
69524 Y值范围为804至1599,所以1000可能在其中，需要读取
 
69535 X值为1的leaf block的结束BLOCK，及X值为2的leaf block的起始BLOCK
 
69536 Y值范围为363至1158,所以1000可能在其中，需要读取
69548 X值为2的leaf block的结束BLOCK

查询，y between 1000 and 2500 读取顺序为：

69522->69523->69524->69525->69526->69535->69536->69537->69538->69548
 
69522 Branch block
 
69523         X值为1的leaf block的起始BLOCK
69524 - 69526 Y值范围为804至3191,“y between 1000 and 2500”可能在其中，需要读取
 
69535         X值为1的leaf block的结束BLOCK，及X值为2的leaf block的起始BLOCK
 
69536 - 69538 Y值范围为363至2750,“y between 1000 and 2500”可能在其中，需要读取
69548         X值为2的leaf block的结束BLOCK

我们可以这样认为，INDEX SKIP SCAN就相当于要在X=1及X=2的两个子索引(也可以说是虚拟索引）中进行扫索，因而，前导列X如果有多个不同的值，就要在多个不同的子索引中进行扫描，个数越多，代价也越高，优化器最终也就不会选择使用INDEX SKIP SCAN了。例如，我们更改X的唯一值为10000，查询就不再采用INDEX SKIP SCAN：

SQL> exec dbms_stats.SET_COLUMN_STATS('SYS','TEST','X',DISTCNT=>10000);
 
PL/SQL procedure successfully completed.
 
SQL> select * from test where y=1000;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 1066898076
 
-------------------------------------------------------------------------------
| Id  | Operation            | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |        |   100 |   600 |   389K  (1)| 14:06:10 |
|*  1 |  INDEX FAST FULL SCAN| IND_XY |   100 |   600 |   389K  (1)| 14:06:10 |
-------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("Y"=1000)