当前位置: DBARoad > SQL优化 | 性能优化 > 文章正文

常见索引扫描浅析（一）：INDEX RANGE SCAN

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

本文主要通过设置10046事件及Tree Dump等方式，来观察索引扫描。

查看执行计划：

SQL> set autot trace exp
SQL> select id from test
  2  where id between 1000 and 2000;

Execution Plan
----------------------------------------------------------
Plan hash value: 3370510359

--------------------------------------------------------------------------------
| Id  | Operation        | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |             |  2005 | 10025 |     6   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| IND_TEST_ID |  2005 | 10025 |     6   (0)| 00:00:01 |
--------------------------------------------------------------------------------

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

   1 - access("ID">=1000 AND "ID"< =2000)

通过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 id from test       
  2  where id between 1000 and 2000;
 
$ cat emrep_ora_20424.trc | grep "db file s"
WAIT #3: nam='db file sequential read' ela= 52 file#=1 block#=68986 blocks=1 obj#=93996 tim=26719889543765
WAIT #3: nam='db file sequential read' ela= 20 file#=1 block#=68991 blocks=1 obj#=93996 tim=26719889543862
WAIT #3: nam='db file sequential read' ela= 97 file#=1 block#=68992 blocks=1 obj#=93996 tim=26719889561064
WAIT #3: nam='db file sequential read' ela= 25 file#=1 block#=69297 blocks=1 obj#=93996 tim=26719889579472
WAIT #3: nam='db file sequential read' ela= 83 file#=1 block#=69298 blocks=1 obj#=93996 tim=26719889598693
WAIT #3: nam='db file sequential read' ela= 17 file#=1 block#=69299 blocks=1 obj#=93996 tim=26719889617384

读取顺序为：block 68986 -> 68991 -> 68992 -> 69297 -> 69298 -> 69299
对索引IND_TEST_ID做TREEDUMP：

----- begin tree dump
branch: 0x410d7a 4263290 (0: nrow: 256, level: 1)       ##对应BLOCK：68986
   leaf: 0x410d7b 4263291 (-1: nrow: 491 rrow: 491)
   leaf: 0x410d7c 4263292 (0: nrow: 477 rrow: 477)
   leaf: 0x410d7d 4263293 (1: nrow: 478 rrow: 478)
   leaf: 0x410d7e 4263294 (2: nrow: 477 rrow: 477)
   leaf: 0x410d7f 4263295 (3: nrow: 478 rrow: 478)      ##对应BLOCK：68991
   leaf: 0x410d80 4263296 (4: nrow: 477 rrow: 477)      ##对应BLOCK：68992
   leaf: 0x410eb1 4263601 (5: nrow: 477 rrow: 477)      ##对应BLOCK：69297
   leaf: 0x410eb2 4263602 (6: nrow: 478 rrow: 478)      ##对应BLOCK：69298
   leaf: 0x410eb3 4263603 (7: nrow: 477 rrow: 477)      ##对应BLOCK：69299
   leaf: 0x410eb4 4263604 (8: nrow: 477 rrow: 477)
   leaf: 0x410eb5 4263605 (9: nrow: 478 rrow: 478)
   leaf: 0x410eb6 4263606 (10: nrow: 477 rrow: 477)
   leaf: 0x410eb7 4263607 (11: nrow: 478 rrow: 478)
   leaf: 0x410eb8 4263608 (12: nrow: 477 rrow: 477)
   leaf: 0x410ed9 4263641 (13: nrow: 477 rrow: 477)
   leaf: 0x410eda 4263642 (14: nrow: 478 rrow: 478)
   leaf: 0x410edb 4263643 (15: nrow: 477 rrow: 477)
   .................................
   .................................
   省略部份leaf
   .................................
   .................................
   leaf: 0x411084 4264068 (248: nrow: 448 rrow: 448)
   leaf: 0x411085 4264069 (249: nrow: 448 rrow: 448)
   leaf: 0x411086 4264070 (250: nrow: 448 rrow: 448)
   leaf: 0x411087 4264071 (251: nrow: 448 rrow: 448)
   leaf: 0x411088 4264072 (252: nrow: 448 rrow: 448)
   leaf: 0x411089 4264073 (253: nrow: 448 rrow: 448)
   leaf: 0x41108a 4264074 (254: nrow: 388 rrow: 388)
----- end tree dump

通过dbms_utility的data_block_address_file、data_block_address_block函数转换，可以找到叶子节点、分枝节点对应的数据文件和BLOCK,例如：

SQL> select
  2  dbms_utility.data_block_address_file(4263290) file_id#,
  3  dbms_utility.data_block_address_block(4263290) block_id#
  4  from dual;

  FILE_ID#  BLOCK_ID#
---------- ----------
         1      68986

SQL> select
  2  dbms_utility.data_block_address_file(4263603) file_id#,
  3  dbms_utility.data_block_address_block(4263603) block_id#
  4  from dual;

  FILE_ID#  BLOCK_ID#
---------- ----------
         1      69299

通过10046 trace的BLOCK读取顺序与TREEDUMP相应的节点进行对应（红色字体部份），我们可以看出，INDEX RANGE SCAN扫描顺序为：先读取branch节点，找到相应的leaf节点，再一个leaf一个leaf地顺序读取（如果要到表中读取数据，则按ROWID进行读取，再读取下一个leaf，即TABLE ACCESS BY INDEX ROWID），因而取出来的数据是有序的。如果按索引列进行排序，可以观察到实际上并没有排序操作：

SQL> set autot trace exp
SQL> select id from test
  2  where id between 1000 and 2000
  3  order by id;

Execution Plan
----------------------------------------------------------
Plan hash value: 3370510359

--------------------------------------------------------------------------------
| Id  | Operation        | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |             |  2005 | 10025 |     6   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| IND_TEST_ID |  2005 | 10025 |     6   (0)| 00:00:01 |
--------------------------------------------------------------------------------

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

   1 - access("ID">=1000 AND "ID"< =2000)
       filter("ID"<=2000 AND "ID">=1000)

观察10046 trace读取的BLOK顺序，与不加order by 一样:

$ cat emrep_ora_26552.trc | grep "db file s"
WAIT #3: nam='db file sequential read' ela= 950 file#=1 block#=68986 blocks=1 obj#=93996 tim=26721534860228
WAIT #3: nam='db file sequential read' ela= 20 file#=1 block#=68991 blocks=1 obj#=93996 tim=26721534860332
WAIT #3: nam='db file sequential read' ela= 87 file#=1 block#=68992 blocks=1 obj#=93996 tim=26721534877991
WAIT #3: nam='db file sequential read' ela= 24 file#=1 block#=69297 blocks=1 obj#=93996 tim=26721534897486
WAIT #3: nam='db file sequential read' ela= 66 file#=1 block#=69298 blocks=1 obj#=93996 tim=26721534917240
WAIT #3: nam='db file sequential read' ela= 18 file#=1 block#=69299 blocks=1 obj#=93996 tim=26721534936685

同样，如果我们加上order by id desc，索引扫描方式仅仅是读取叶子节点顺序相反了：

SQL> set autot trace exp
SQL> select id from test
  2  where id between 1000 and 2000
  3  order by id desc;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3128577303
 
-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |  2005 | 10025 |     6   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN DESCENDING| IND_TEST_ID |  2005 | 10025 |     6   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("ID">=1000 AND "ID"< =2000)
       filter("ID"<=2000 AND "ID">=1000)
 
$ cat emrep_ora_27566.trc | grep "db file s"
WAIT #7: nam='db file sequential read' ela= 63 file#=1 block#=68986 blocks=1 obj#=93996 tim=26721938632913
WAIT #7: nam='db file sequential read' ela= 21 file#=1 block#=69299 blocks=1 obj#=93996 tim=26721938632989
WAIT #7: nam='db file sequential read' ela= 22 file#=1 block#=69298 blocks=1 obj#=93996 tim=26721938640304
WAIT #7: nam='db file sequential read' ela= 21 file#=1 block#=69297 blocks=1 obj#=93996 tim=26721938659650
WAIT #7: nam='db file sequential read' ela= 22 file#=1 block#=68992 blocks=1 obj#=93996 tim=26721938679280
WAIT #7: nam='db file sequential read' ela= 21 file#=1 block#=68991 blocks=1 obj#=93996 tim=26721938698543

— The End —

关键字:
上一篇 10G之ORA_ROWSCN
常见索引扫描浅析（二）：FFS 下一篇
喜欢DBARoad的文章，那就通过 RSS Feed 功能订阅阅读吧！

相关文章

随机文章

我要评论

*

* 绝不会泄露


相关文章

最新文章

  • 订阅


      抓虾
      google reader
      my yahoo
      bloglines
      鲜果
      有道

站内搜索



返回首页 | 关于我 | 联系我 | 广告合作 | 网站地图 | 友情链接 | 版权声明 |