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