select index oracle,oracle index索引结构（一）

--分析索引的构造关系

--查询索引的对象号

SQL> select object_id from dba_objects where object_name='IDX_T_DETAIL';

OBJECT_ID

----------

69559

--转储索引， 69559即为上述查询的信息

SQL> alter system set events 'immediate trace name treedump level 69559';

System altered

--trace文件如下：一个分支块，若干叶子块

branch: 0x2843803 42219523 (0: nrow: 21, level: 1) --level也可知分支块的为几级，nrow即为叶子块的个数,level为从此块到leaf的高度，故leaf无level

leaf: 0x2843804 42219524 (-1: nrow: 485 rrow: 485) -- -1表明是同一层叶子块的第一个叶子块

leaf: 0x2843805 42219525 (0: nrow: 479 rrow: 479)

leaf: 0x2843806 42219526 (1: nrow: 479 rrow: 479)

leaf: 0x2843807 42219527 (2: nrow: 479 rrow: 479)

leaf: 0x2843808 42219528 (3: nrow: 479 rrow: 479)

leaf: 0x2843809 42219529 (4: nrow: 479 rrow: 479)

leaf: 0x284380a 42219530 (5: nrow: 479 rrow: 479)

leaf: 0x284380b 42219531 (6: nrow: 479 rrow: 479)

leaf: 0x284380c 42219532 (7: nrow: 479 rrow: 479)

leaf: 0x284380d 42219533 (8: nrow: 479 rrow: 479)

leaf: 0x284380e 42219534 (9: nrow: 479 rrow: 479)

leaf: 0x284380f 42219535 (10: nrow: 479 rrow: 479)

leaf: 0x2843811 42219537 (11: nrow: 479 rrow: 479)

leaf: 0x2843812 42219538 (12: nrow: 479 rrow: 479)

leaf: 0x2843813 42219539 (13: nrow: 479 rrow: 479)

leaf: 0x2843814 42219540 (14: nrow: 479 rrow: 479)

leaf: 0x2843815 42219541 (15: nrow: 479 rrow: 479)

leaf: 0x2843816 42219542 (16: nrow: 479 rrow: 479)

leaf: 0x2843817 42219543 (17: nrow: 479 rrow: 479)

leaf: 0x2843818 42219544 (18: nrow: 479 rrow: 479)

leaf: 0x2843819 42219545 (19: nrow: 414 rrow: 414)

--转储上述一个叶子块 leaf: 0x2843804 42219524 (-1: nrow: 485 rrow: 485)

---参数值即为叶子块的十进制地址

SQL> select dbms_utility.data_block_address_file(42219524) as 叶子块所在的文件号,

2         dbms_utility.data_block_address_block(42219524) as 叶子块所在的数据块号

3  from dual;

叶子块所在的文件号           叶子块所在的数据块号

------------------ --------------------

10               276484

--转储下上述的叶子块，内部构成

SQL> alter system dump datafile 10  block 276484;

System altered

--trace内容如下，可知对应表记录数为485，与上述的叶子块 leaf: 0x2843804 42219524 (-1: nrow: 485 rrow: 485)一致

--说明此叶子块存储对应的表记录数为485

row#0[8024] flag: ------, lock: 0, len=12

col 0; len 2; (2):  c1 02

col 1; len 6; (6):  02 83 5c ec 00 00

row#1[8012] flag: ------, lock: 0, len=12

col 0; len 2; (2):  c1 03

col 1; len 6; (6):  02 83 5c ec 00 01

row#2[8000] flag: ------, lock: 0, len=12

col 0; len 2; (2):  c1 04

col 1; len 6; (6):  02 83 5c ec 00 02

row#3[7988] flag: ------, lock: 0, len=12

col 0; len 2; (2):  c1 05

col 1; len 6; (6):  02 83 5c ec 00 03

中间内容从略

row#484[1834] flag: ------, lock: 0, len=13

col 0; len 3; (3):  c2 05 56

col 1; len 6; (6):  02 83 5c ec 01 e4

----- end of leaf block dump -----

End dump data blocks tsn: 8 file#: 10 minblk 276484 maxblk 276484

--也可把treedump命令用于oradebug之中，好处，直接看到生成的trace文件所在路径及名称

SQL> conn sys/system as sysdba

Connected.

SQL> oradebug setmypid

Statement processed.

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

Session altered.

SQL> oradebug tracefile_name

d:\oracle11g_64bit\diag\rdbms\second\second\trace\second_ora_4444.trc