dump b*tree内容解析

来源：互联网发布：小米个性主题软件编辑：程序博客网时间：2024/05/18 01:34

SQL> create table test as select * from dba_objects;

SQL> create index ind_object_name on test(object_name);

SQL> select index_name,s.blevel from user_indexes s where s.index_name='IND_OBJECT_NAME';

INDEX_NAME                               BLEVEL
------------------------------                 ----------
IND_OBJECT_NAME                         2

SQL> select object_id from dba_objects s where s.object_name='IND_OBJECT_NAME';

OBJECT_ID
----------
73522

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

会话已更改。

在trace的目录中找到对应的dump文件

----- begin tree dump
branch: 0x1002ab3 16788147 (0: nrow: 3, level: 2)

第一个0的意思是position within previous level block(starting at -1 except root starting at 0)，翻译root是从0开始，其他的从-1开始；

nrow: number of all index entries 总的索引的数量；

rrows: number of current index entries 当前索引的数量；

level: branch block level(leaf block implicitly 0) 枝节点数据块的层次，叶子节点暗含是0；

16788147（八进制）可以转换为数据块的位置，如下所示；

       0x1002ab3（十六进制），可以与16788147相互转换。
   branch: 0x1002f04 16789252 (-1: nrow: 244, level: 1)
      leaf: 0x1002ab4 16788148 (-1: nrow: 187 rrow: 187)
      leaf: 0x1002ab5 16788149 (0: nrow: 181 rrow: 181)
      leaf: 0x1002ab6 16788150 (1: nrow: 183 rrow: 183)
      leaf: 0x1002ab7 16788151 (2: nrow: 185 rrow: 185)
      leaf: 0x1002ab8 16788152 (3: nrow: 187 rrow: 187)
      leaf: 0x1002ab9 16788153 (4: nrow: 189 rrow: 189)
      leaf: 0x1002aba 16788154 (5: nrow: 190 rrow: 190)
      leaf: 0x1002abb 16788155 (6: nrow: 190 rrow: 190)
      leaf: 0x1002abc 16788156 (7: nrow: 185 rrow: 185)
      leaf: 0x1002abd 16788157 (8: nrow: 184 rrow: 184)
      leaf: 0x1002abe 16788158 (9: nrow: 187 rrow: 187)
      leaf: 0x1002abf 16788159 (10: nrow: 184 rrow: 184)
      leaf: 0x1002ac1 16788161 (11: nrow: 181 rrow: 181)
      leaf: 0x1002ac2 16788162 (12: nrow: 189 rrow: 189)

..................................省略部分内容........................................................

   branch: 0x1003016 16789526 (0: nrow: 269, level: 1)
      leaf: 0x1002f03 16789251 (-1: nrow: 187 rrow: 187)
      leaf: 0x1002f05 16789253 (0: nrow: 189 rrow: 189)
      leaf: 0x1002f06 16789254 (1: nrow: 190 rrow: 190)
      leaf: 0x1002f07 16789255 (2: nrow: 188 rrow: 188)
      leaf: 0x1002f08 16789256 (3: nrow: 190 rrow: 190)
      leaf: 0x1002f09 16789257 (4: nrow: 186 rrow: 186)
      leaf: 0x1002f0a 16789258 (5: nrow: 183 rrow: 183)
      leaf: 0x1002f0b 16789259 (6: nrow: 183 rrow: 183)
      leaf: 0x1002f0c 16789260 (7: nrow: 182 rrow: 182)
      leaf: 0x1002f0d 16789261 (8: nrow: 186 rrow: 186)
      leaf: 0x1002f0e 16789262 (9: nrow: 186 rrow: 186)
      leaf: 0x1002f0f 16789263 (10: nrow: 184 rrow: 184)

..................................省略部分内容........................................................

   branch: 0x10030e0 16789728 (1: nrow: 200, level: 1)
      leaf: 0x1003015 16789525 (-1: nrow: 260 rrow: 260)
      leaf: 0x1003017 16789527 (0: nrow: 248 rrow: 248)
      leaf: 0x1003018 16789528 (1: nrow: 269 rrow: 269)
      leaf: 0x1003019 16789529 (2: nrow: 212 rrow: 212)
      leaf: 0x100301a 16789530 (3: nrow: 287 rrow: 287)
      leaf: 0x100301b 16789531 (4: nrow: 312 rrow: 312)
      leaf: 0x100301c 16789532 (5: nrow: 260 rrow: 260)
      leaf: 0x100301d 16789533 (6: nrow: 218 rrow: 218)
      leaf: 0x100301e 16789534 (7: nrow: 194 rrow: 194)
      leaf: 0x100301f 16789535 (8: nrow: 194 rrow: 194)
      leaf: 0x1003020 16789536 (9: nrow: 194 rrow: 194)

..................................省略部分内容........................................................

----- end tree dump

SQL> select dbms_utility.data_block_address_file(16788154) "file",
2 dbms_utility.data_block_address_block(16788154) "block"
3 from dual;

file block
---------- ----------
4 10938