索引全扫的操作流程

原创唐祖亮 2020-04-05

774

oarcle中索引是使用频率很高的数据库对象，而在使用索引的时候对索引的访问方式有好几种，这里我来介绍一下索引全扫（index full scan）的操作流程。
索引全扫一般出现在使用索引列进行排序的时候，当使用正序排序的时候会先扫描索引的根节点块，接着扫描最左侧的分枝节点块，然后扫描最左侧的叶子节点块，最后依次扫描到最后一个叶子块。期间只有开始会扫描根节点块和分枝节点块，当扫描到最左侧叶子节点块之后会根据叶子节点的指针直接扫描到最后一个叶子块，不会再去扫描其他分枝节点块。如果使用倒序排序那么就会从根节点块到最右侧分枝节点块到最右侧叶子节点块然后往前扫描到第一个叶子节点块。

流程示意图

下面做一个测试，看看是否如我所描述的那样。

–创建测试表

SQL> create table test_index (id varchar2(200),name varchar2(200),tel varchar2(200));

Table created.

–插入数据

SQL> insert into test_index select rpad('1'||level,200,'0'),(rpad(('tzl'||level),200,0)),rpad('1',200,'0') from dual connect by level<10000;

9999 rows created.

SQL> commit;

Commit complete.

–创建索引

SQL> create index test_index_1 on test_index(id,name);

Index created.

–收集统计信息

SQL> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'TEST_INDEX',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='TEST_INDEX_1' and table_name='TEST_INDEX';

TABLE_NAME                     INDEX_NAME                         BLEVEL INDEX_TYPE                  LEAF_BLOCKS
------------------------------ ------------------------------ ---------- --------------------------- -----------
TEST_INDEX                     TEST_INDEX_1                            2 NORMAL                              589

–执行一个走索引全扫描的查询语句，并查看其执行计划。

SQL> alter session set statistics_level=all;

Session altered.

SQL>select id from test_index where id is not null order by id;

省略查询结果

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  5rqyuk2wvv056, child number 0
-------------------------------------
select id from test_index where id is not null order by id

Plan hash value: 2199929962

-------------------------------------------------------------------------------------------
| Id  | Operation        | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |              |      1 |        |   9999 |00:00:00.01 |    1219 |
|*  1 |  INDEX FULL SCAN | TEST_INDEX_1 |      1 |   9999 |   9999 |00:00:00.01 |    1219 |
-------------------------------------------------------------------------------------------

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

   1 - filter("ID" IS NOT NULL)


18 rows selected.

–利用10200事件号进行事件追踪。

SQL> alter session set tracefile_identifier='index_fs'; 

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

Session altered.

SQL>select id from test_index where id is not null order by id;
.......省略查询结果
SQL> alter session set events '10200 trace name context off';

Session altered.

–找到trace文件查看SQL语句执行时访问的块。

SQL> select value from v$diag_info where name like '%Default%';

VALUE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/orcl/ORCL/trace/ORCL_ora_7601_index_fs.trc

–截取其中部分内容

ktrgtc2(): started for block <0x0004 : 0x01000323> objd: 0x000123d6
ktrgtc2(): started for block <0x0004 : 0x0100412b> objd: 0x000123d6
ktrget2(): started for block  <0x0004 : 0x01000324> objd: 0x000123d6
ktrget2(): started for block  <0x0004 : 0x01000324> objd: 0x000123d6
ktrget2(): started for block  <0x0004 : 0x01000324> objd: 0x000123d6
ktrget2(): started for block  <0x0004 : 0x01000325> objd: 0x000123d6
ktrget2(): started for block  <0x0004 : 0x01000325> objd: 0x000123d6
ktrget2(): started for block  <0x0004 : 0x01000326> objd: 0x000123d6
ktrget2(): started for block  <0x0004 : 0x01000326> objd: 0x000123d6
ktrget2(): started for block  <0x0004 : 0x01000327> objd: 0x000123d6
ktrget2(): started for block  <0x0004 : 0x01000327> objd: 0x000123d6
ktrget2(): started for block  <0x0004 : 0x01000328> objd: 0x000123d6
ktrget2(): started for block  <0x0004 : 0x01000328> objd: 0x000123d6
ktrget2(): started for block  <0x0004 : 0x01000329> objd: 0x000123d6
ktrget2(): started for block  <0x0004 : 0x01000329> objd: 0x000123d6
 ......中间省略部分内容
ktrget2(): started for block  <0x0004 : 0x01004128> objd: 0x000123d6
ktrget2(): started for block  <0x0004 : 0x01004129> objd: 0x000123d6
ktrget2(): started for block  <0x0004 : 0x01004129> objd: 0x000123d6
ktrget2(): started for block  <0x0004 : 0x0100412a> objd: 0x000123d6
ktrget2(): started for block  <0x0004 : 0x0100412a> objd: 0x000123d6
 ......中间省略部分内容
ktrget2(): started for block  <0x0004 : 0x010042e1> objd: 0x000123d6
ktrget2(): started for block  <0x0004 : 0x010042e2> objd: 0x000123d6
ktrget2(): completed for  block <0x0004 : 0x010042e2> objd: 0x000123d6

–转储出索引TEST_INDEX_1

SQL> select object_name,object_id from dba_objects where object_name='TEST_INDEX_1';

OBJECT_NAME                             OBJECT_ID
-------------------------------------- ----------
TEST_INDEX_1                             74710

SQL> alter session set tracefile_identifier='TEST_INDEX_1';

Session altered.

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

Session altered.

SQL> select value from v$diag_info where name like '%Default%';

VALUE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/orcl/ORCL/trace/ORCL_ora_7601_TEST_INDEX_1.trc

–打开转储出的文件，截取了其中的部分内容

----- begin tree dump
branch: 0x1000323 16778019 (0: nrow: 4, level: 2)
   branch: 0x100412b 16793899 (-1: nrow: 157, level: 1)
      leaf: 0x1000324 16778020 (-1: nrow: 17 rrow: 17)
      leaf: 0x1000325 16778021 (0: nrow: 17 rrow: 17)
      leaf: 0x1000326 16778022 (1: nrow: 17 rrow: 17)
      leaf: 0x1000327 16778023 (2: nrow: 17 rrow: 17)
      leaf: 0x1000328 16778024 (3: nrow: 17 rrow: 17)
      leaf: 0x1000329 16778025 (4: nrow: 17 rrow: 17)
      leaf: 0x100032a 16778026 (5: nrow: 17 rrow: 17)
      leaf: 0x100032b 16778027 (6: nrow: 17 rrow: 17)
      leaf: 0x100032c 16778028 (7: nrow: 17 rrow: 17)
      leaf: 0x100032d 16778029 (8: nrow: 17 rrow: 17)
      leaf: 0x100032e 16778030 (9: nrow: 17 rrow: 17)
      leaf: 0x100032f 16778031 (10: nrow: 17 rrow: 17)
      leaf: 0x1000331 16778033 (11: nrow: 17 rrow: 17)
	    ........中间省略部分内容
      leaf: 0x1004123 16793891 (149: nrow: 17 rrow: 17)
      leaf: 0x1004124 16793892 (150: nrow: 17 rrow: 17)
      leaf: 0x1004125 16793893 (151: nrow: 17 rrow: 17)
      leaf: 0x1004126 16793894 (152: nrow: 17 rrow: 17)
      leaf: 0x1004127 16793895 (153: nrow: 17 rrow: 17)
      leaf: 0x1004128 16793896 (154: nrow: 17 rrow: 17)
      leaf: 0x1004129 16793897 (155: nrow: 17 rrow: 17)
   branch: 0x10041d2 16794066 (0: nrow: 164, level: 1)
      leaf: 0x100412a 16793898 (-1: nrow: 17 rrow: 17)
      leaf: 0x100412c 16793900 (0: nrow: 17 rrow: 17)
      leaf: 0x100412d 16793901 (1: nrow: 17 rrow: 17)
      leaf: 0x100412e 16793902 (2: nrow: 17 rrow: 17)
      leaf: 0x100412f 16793903 (3: nrow: 17 rrow: 17)
      leaf: 0x1004130 16793904 (4: nrow: 17 rrow: 17)
      leaf: 0x1004131 16793905 (5: nrow: 17 rrow: 17)
      leaf: 0x1004132 16793906 (6: nrow: 17 rrow: 17)
      leaf: 0x1004133 16793907 (7: nrow: 17 rrow: 17)
    	  ........中间省略部分内容
      leaf: 0x10042dc 16794332 (109: nrow: 17 rrow: 17)
      leaf: 0x10042dd 16794333 (110: nrow: 17 rrow: 17)
      leaf: 0x10042de 16794334 (111: nrow: 17 rrow: 17)
      leaf: 0x10042df 16794335 (112: nrow: 17 rrow: 17)
      leaf: 0x10042e0 16794336 (113: nrow: 17 rrow: 17)
      leaf: 0x10042e1 16794337 (114: nrow: 17 rrow: 17)
      leaf: 0x10042e2 16794338 (115: nrow: 3 rrow: 3)
----- end tree dump

从10200事件追踪文件中可以看到，访问索引块的顺序是0x01000323->0x0100412b->0x01000324->0x01000325…->0x010042e2这样的顺序访问的，而在访问完第一个分枝节点下的所有叶子块的时候直接就访问了第二个分枝节点下的第一个叶子块，而没有去访问第二个分枝节点块，也就是从截取的10200事件追踪文件中的倒数第三行0x01004129这个索引块之后直接访问了0x0100412a这个叶子块，从索引转储文件中看0x01004129这个叶子块是索引第一个分枝节点下的最后一个叶子块，下面一行就是0x10041d2这个块，这是索引的第二个分枝节点块，而在10200事件追踪文件中并没有访问第二个分枝块，这也能证实开头所说的索引全扫会先访问根节点块然后访问最左侧分枝节点块，再访问最左侧叶子块
接着依次扫描到最后一个叶子块，除了开始扫描的根节点块分枝节点块后面不会再扫描其他的分枝节点块。

接下来测试倒序排序

SQL>select id from test_index where id is not null order by id desc;

省略查询结果
SQL> select * from table(dbms_xplan.display_cursor('','','allstats last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  0686yjs2kybg1, child number 1
-------------------------------------
select id from test_index where id is not null order by id desc

Plan hash value: 501969565

-----------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |              |      1 |        |   9999 |00:00:00.01 |    1219 |
|*  1 |  INDEX FULL SCAN DESCENDING| TEST_INDEX_1 |      1 |   9999 |   9999 |00:00:00.01 |    1219 |
-----------------------------------------------------------------------------------------------------

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

   1 - filter("ID" IS NOT NULL)


18 rows selected.

SQL> alter session set tracefile_identifier='index_fs_d'; 

Session altered.

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

Session altered.

SQL>select id from test_index where id is not null order by id desc;
.......省略查询结果
SQL> alter session set events '10200 trace name context off';

Session altered.

SQL> select value from v$diag_info where name like '%Default%';

VALUE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/orcl/ORCL/trace/ORCL_ora_7601_index_fs_d.trc

–打开转储出的文件，截取了其中的部分内容

ktrgtc2(): started for block <0x0004 : 0x01000323> objd: 0x000123d6
ktrgtc2(): started for block <0x0004 : 0x010042e3> objd: 0x000123d6
ktrget2(): started for block  <0x0004 : 0x010042e2> objd: 0x000123d6
ktrget2(): started for block  <0x0004 : 0x010042e2> objd: 0x000123d6
ktrget2(): started for block  <0x0004 : 0x010042e1> objd: 0x000123d6
ktrget2(): started for block  <0x0004 : 0x010042e1> objd: 0x000123d6
ktrget2(): started for block  <0x0004 : 0x010042e0> objd: 0x000123d6
ktrget2(): started for block  <0x0004 : 0x010042e0> objd: 0x000123d6

–截取索引转储文件中的部分内容

----- begin tree dump
branch: 0x1000323 16778019 (0: nrow: 4, level: 2)
   branch: 0x100412b 16793899 (-1: nrow: 157, level: 1)
      leaf: 0x1000324 16778020 (-1: nrow: 17 rrow: 17)
      leaf: 0x1000325 16778021 (0: nrow: 17 rrow: 17)
      leaf: 0x1000326 16778022 (1: nrow: 17 rrow: 17)
      leaf: 0x1000327 16778023 (2: nrow: 17 rrow: 17)
      leaf: 0x1000328 16778024 (3: nrow: 17 rrow: 17)
      leaf: 0x1000329 16778025 (4: nrow: 17 rrow: 17)
      leaf: 0x100032a 16778026 (5: nrow: 17 rrow: 17)
      leaf: 0x100032b 16778027 (6: nrow: 17 rrow: 17)
	  中间省略部分内容
	branch: 0x10042e3 16794339 (2: nrow: 117, level: 1)
      leaf: 0x100426b 16794219 (-1: nrow: 17 rrow: 17)
      leaf: 0x100426d 16794221 (0: nrow: 17 rrow: 17)
      leaf: 0x100426e 16794222 (1: nrow: 17 rrow: 17)
      leaf: 0x100426f 16794223 (2: nrow: 17 rrow: 17)
      leaf: 0x1004270 16794224 (3: nrow: 17 rrow: 17)
	  中间省略部分内容
	  leaf: 0x10042dc 16794332 (109: nrow: 17 rrow: 17)
      leaf: 0x10042dd 16794333 (110: nrow: 17 rrow: 17)
      leaf: 0x10042de 16794334 (111: nrow: 17 rrow: 17)
      leaf: 0x10042df 16794335 (112: nrow: 17 rrow: 17)
      leaf: 0x10042e0 16794336 (113: nrow: 17 rrow: 17)
      leaf: 0x10042e1 16794337 (114: nrow: 17 rrow: 17)
      leaf: 0x10042e2 16794338 (115: nrow: 3 rrow: 3)
----- end tree dump

从10200事件追踪文件中可以看到，访问索引块的顺序是0x01000323->0x10042e3->0x10042e2->0x10042e1…->0x1000324这样的顺序访问的，在截取的索引转储文件中0x01000323是根节点块，0x10042e3是最后一个分枝节点块，怎么能确定他是最后一个分枝节点块呢，在

branch: 0x10042e3 16794339 (2: nrow: 117, level: 1)

这一条信息中括号里的序号为2，这代表的是该块在所在层级的序号，根节点块是从0开始排的，分枝节点块和叶子块是从-1开始排的，
在根节点branch: 0x1000323 16778019 (0: nrow: 4, level: 2)这条信息中的nrow表示的是在该块中存放了多条下一个层级的信息，这里是4，那么说明存放了
4个分枝节点块的信息，他们的序号分别是-1,0,1，2从这里可以知道0x10042e3这个分枝节点块是该索引的最后一个分枝块，再看最后一行信息

leaf: 0x10042e2 16794338 (115: nrow: 3 rrow: 3)

这里可以看到序号为115，而分枝节点块中的nrow是117，这也能说明0x10042e2这个叶子块是索引中的右侧的叶子块。
这样一结合起来跟前面我说到的使用索引列倒序排序时索引全扫访问索引块的顺序是一至的。

oracle

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

索引全扫的操作流程

评论