索引范围扫描操作流程简单介绍

索引范围扫描就是按照根、枝、叶的顺序读取，然后根据读取到的满足条件的数据的ROWID回到表中读取数据，如果要查询的数据列包含在索引中那么就免去了回表这步骤。叶块的地址在枝块，枝块地址在根块。找到枝块就可以找到叶块，找到根块就可以找到枝块。那么，如何找到根块呢？

其实很简单，在Oracle中，根块永远在索引段头的下一个块处。因此，索引扫描是不必读取索引段头的。先在数据字典表中找到段头位置，块号加1就是根块位置了。
接下来测试看看
–创建一个测试表

SQL> create table t11 as select *  from dba_objects;

Table created.

–创建索引

SQL> create index ind_t11 on t11(object_id);

Index created.

–收集统计信息

SQL> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'T11',estimate_percent=>100,cascade=>true,method_opt=>'for all columns size auto',no_invalidate=>false);

PL/SQL procedure successfully completed.

–查看索引信息

SQL> select table_name,index_name,blevel,index_type,leaf_blocks from dba_indexes where index_name='IND_T11' and table_name='T11';

TABLE_NAME                     INDEX_NAME                         BLEVEL INDEX_TYPE                  LEAF_BLOCKS
------------------------------ ------------------------------ ---------- --------------------------- -----------
T11                            IND_T11                                 1 NORMAL                              161

–执行一个简单查询查看执行计划

SQL> select * from table(dbms_xplan.display_cursor('','','allstats last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  g7411gwcvppnd, child number 0
-------------------------------------
select * from t11 where object_id=11

Plan hash value: 469757982

-------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |      1 |        |      1 |00:00:00.01 |       4 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T11     |      1 |      1 |      1 |00:00:00.01 |       4 |
|*  2 |   INDEX RANGE SCAN          | IND_T11 |      1 |      1 |      1 |00:00:00.01 |       3 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"=11)


19 rows selected.

从执行计划可以看到在索引范围扫描这一步消耗了3个逻辑读，而索引的层高为1，说明有两层
观察到的逻辑读为4。这4次逻辑读分别是：Root块一次，叶块两次，回表读取数据块一次。
叶块之所以需要两次，是因为索引是非唯一的。第一次读叶块是为了取出目标行ROWID，第二次读叶块是判断此叶块中还有没有满足条件的行。
如果建成了唯一索引，不需要判断叶块是否还有满足条件的行，叶块就只需要读一次，一共只需要3次逻辑读。

drop index ind_t11;
SQL> drop index ind_t11;

Index dropped.

create unique index ind_t11_1 on t11(object_id);
SQL> create unique index ind_t11_1 on t11(object_id);

Index created.

select * from t11 where object_id=11;
SQL> select * from table(dbms_xplan.display_cursor('','','allstats last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  g7411gwcvppnd, child number 0
-------------------------------------
select * from t11 where object_id=11

Plan hash value: 645999193

------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |      1 |        |      1 |00:00:00.01 |       3 |      4 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T11       |      1 |      1 |      1 |00:00:00.01 |       3 |      4 |
|*  2 |   INDEX UNIQUE SCAN         | IND_T11_1 |      1 |      1 |      1 |00:00:00.01 |       2 |      4 |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"=11)


19 rows selected.

下面通过内部事件号10200追踪该SQL执行所访问的块，验证是否真的按照根节点–>分枝节点–>叶子节点这样的顺序来访问的。

SQL>alter session set events '10200 trace name context forever,level 1';

Session altered.

SQL>select * from t11 where object_id=11;
......省略查询结果
SQL>alter session set events '10200 trace name context off';

Session altered.

截取其中部分内容如下：

ktrgtc2(): started for block <0x0004 : 0x0100331b> objd: 0x0001236d
  env: (scn: 0x0000.0012f7db  xid: 0x000a.004.00000271  uba: 0x00c03029.012c.06  statement num=0  parent xid: xid: 0x0000.000.00000000  scn: 0x0000.0012ef71 97sch: scn: 0x0000.00000000  mascn: (scn: 0x0000.0012ef5d)
ktrexc(): returning 2 on:  0xa9b95fc  scn: 0xffff.ffffffff  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  scn: 0xffff.ffffffff  sfl: 0
ktrgtc2(): completed for block <0x0004 : 0x0100331b> objd: 0x0001236d
ktrgtc2(): started for block <0x0004 : 0x0100331c> objd: 0x0001236d
  env: (scn: 0x0000.0012f7db  xid: 0x000a.004.00000271  uba: 0x00c03029.012c.06  statement num=0  parent xid: xid: 0x0000.000.00000000  scn: 0x0000.0012ef71 98sch: scn: 0x0000.0012f7db  mascn: (scn: 0x0000.0012ef5d)
ktrexc(): returning 2 on:  0xa9b95fc  scn: 0xffff.ffffffff  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  scn: 0xffff.ffffffff  sfl: 0
ktrgtc2(): completed for block <0x0004 : 0x0100331c> objd: 0x0001236d
ktrgtc2(): started for block <0x0004 : 0x01002c13> objd: 0x00012367
  env: (scn: 0x0000.0012f7db  xid: 0x000a.004.00000271  uba: 0x00c03029.012c.06  statement num=0  parent xid: xid: 0x0000.000.00000000  scn: 0x0000.0012ef71 98sch: scn: 0x0000.0012f7db  mascn: (scn: 0x0000.0012ef5d)
ktrexc(): returning 2 on:  0xa9b95fc  scn: 0xffff.ffffffff  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  scn: 0xffff.ffffffff  sfl: 0
ktrgtc2(): completed for block <0x0004 : 0x01002c13> objd: 0x00012367

可以看到依次访问了0x0100331b、0x0100331c、0x01002c13这三个块。
下面查看转储后的索引ind_t11_1根节点的信息。
查出索引的object_id

SQL>select object_id from user_objects where object_name='IND_T11_1';
74601

通过object_id转储出索引的信息：

SQL>alter session set events 'immediate trace name treedump level 74601';

Session altered.

截取部分内容如下：

----- begin tree dump
branch: 0x100331b 16790299 (0: nrow: 151, level: 1)
   leaf: 0x100331c 16790300 (-1: nrow: 520 rrow: 520)
   leaf: 0x100331d 16790301 (0: nrow: 513 rrow: 513)
   leaf: 0x100331e 16790302 (1: nrow: 513 rrow: 513)
   leaf: 0x100331f 16790303 (2: nrow: 513 rrow: 513)
   leaf: 0x1003320 16790304 (3: nrow: 513 rrow: 513)
   leaf: 0x1003321 16790305 (4: nrow: 513 rrow: 513)
   leaf: 0x1003322 16790306 (5: nrow: 513 rrow: 513)
   leaf: 0x1003323 16790307 (6: nrow: 513 rrow: 513)
   leaf: 0x1003324 16790308 (7: nrow: 513 rrow: 513)
   leaf: 0x1003325 16790309 (8: nrow: 513 rrow: 513)
   leaf: 0x1003326 16790310 (9: nrow: 513 rrow: 513)
   leaf: 0x1003327 16790311 (10: nrow: 513 rrow: 513)
   leaf: 0x1003329 16790313 (11: nrow: 513 rrow: 513)
   leaf: 0x100332a 16790314 (12: nrow: 513 rrow: 513)
   leaf: 0x100332b 16790315 (13: nrow: 513 rrow: 513)
   leaf: 0x100332c 16790316 (14: nrow: 513 rrow: 513)
   leaf: 0x100332d 16790317 (15: nrow: 513 rrow: 513)
   leaf: 0x100332e 16790318 (16: nrow: 513 rrow: 513)
   leaf: 0x100332f 16790319 (17: nrow: 510 rrow: 510)
   leaf: 0x1003330 16790320 (18: nrow: 479 rrow: 479)

通过根节点的十进制地址16790299查到所在的文件号和块号。

SQL>select dbms_utility.data_block_address_file(16790299),dbms_utility.data_block_address_block(16790299) from dual;
     4                                           13083

通过文件号和块号转储出根节点的信息。

SQL>alter system dump datafile 4 block 13083; 

Session altered.

截取部分内容

Block header dump:  0x0100331b
 Object id on Block? Y
 seg/obj: 0x1236d  csc: 0x00.12817c  itc: 1  flg: E  typ: 2 - INDEX
     brn: 0  bdba: 0x1003318 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0xffff.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.0012817c
Branch block dump
=================
header address 139691464981068=0x7f0c7425fa4c
kdxcolev 1
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 1
kdxcosdc 0
kdxconro 150
kdxcofbo 328=0x148
kdxcofeo 6725=0x1a45
kdxcoavs 6397
kdxbrlmc 16790300=0x100331c
kdxbrsno 0
kdxbrbksz 8056 
kdxbr2urrc 0
row#0[8048] dba: 16790301=0x100331d
col 0; len 3; (3):  c2 06 30
row#1[8041] dba: 16790302=0x100331e
col 0; len 2; (2):  c2 0c
row#2[8033] dba: 16790303=0x100331f
col 0; len 3; (3):  c2 11 10
row#3[8025] dba: 16790304=0x1003320
col 0; len 3; (3):  c2 16 1d
row#4[8017] dba: 16790305=0x1003321
col 0; len 3; (3):  c2 1b 2a
row#5[8009] dba: 16790306=0x1003322
col 0; len 3; (3):  c2 20 37
row#6[8001] dba: 16790307=0x1003323
col 0; len 3; (3):  c2 25 44
row#7[7993] dba: 16790308=0x1003324
col 0; len 3; (3):  c2 2a 51
row#8[7985] dba: 16790309=0x1003325

从上面转储的根节点的信息可以看到根节点的地址为0x0100331b、第一个分枝块地址为0x100331c，由于该索引只有两层所以分枝块也是叶子块。
这对比前面追踪事件里看到的0x0100331b、0x0100331c、0x01002c13的顺序来看也能说明，索引范围扫描是从根节点到分枝节点到叶子节点这样的顺序访问的，最后的0x01002c13是回表访问的块。