index leaf/branch block说明

-----根据索引的object_id来treedump其信息
alter system flush buffer_cache;
select object_id from dba_objects where object_name='IDX01_T_OBJECTS' and object_type='INDEX';
alter system set events ' immediate trace name treedump level 21214';

---index root/branch/leaf information
race file /u01/app/oracle/diag/rdbms/orcl11g/orcl11g/trace/orcl11g_ora_2183.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_home
System name:    Linux
Node name:      vm010148
Release:        2.6.18-194.el5
Version:        #1 SMP Tue Mar 16 21:52:39 EDT 2010
Machine:        x86_64
VM name:        VMWare Version: 6
Instance name: orcl11g
Redo thread mounted by this instance: 1
Oracle process number: 23
Unix process pid: 2183, image: oracle@vm010148 (TNS V1-V3)
*** 2014-11-19 16:45:31.137
*** SESSION ID:(924.7257) 2014-11-19 16:45:31.137
*** CLIENT ID:() 2014-11-19 16:45:31.137
*** SERVICE NAME:(SYS$USERS) 2014-11-19 16:45:31.137
*** MODULE NAME:(SQL*Plus) 2014-11-19 16:45:31.137
*** ACTION NAME:() 2014-11-19 16:45:31.137
----- begin tree dump
branch: 0x1000089 16777353 (0: nrow: 29, level: 1)
   leaf: 0x100008a 16777354 (-1: nrow: 485 rrow: 485)
   leaf: 0x100008b 16777355 (0: nrow: 478 rrow: 478)
   leaf: 0x100008c 16777356 (1: nrow: 479 rrow: 479)
   leaf: 0x100008d 16777357 (2: nrow: 478 rrow: 478)
   leaf: 0x100008e 16777358 (3: nrow: 479 rrow: 479)
   leaf: 0x100008f 16777359 (4: nrow: 479 rrow: 479)
   leaf: 0x1000090 16777360 (5: nrow: 479 rrow: 479)
   leaf: 0x1000091 16777361 (6: nrow: 479 rrow: 479)
   leaf: 0x1000092 16777362 (7: nrow: 478 rrow: 478)
   leaf: 0x1000093 16777363 (8: nrow: 479 rrow: 479)
   leaf: 0x1000094 16777364 (9: nrow: 479 rrow: 479)
   leaf: 0x1000095 16777365 (10: nrow: 478 rrow: 478)
   leaf: 0x1000096 16777366 (11: nrow: 479 rrow: 479)
   leaf: 0x1000097 16777367 (12: nrow: 479 rrow: 479)
   leaf: 0x1000098 16777368 (13: nrow: 479 rrow: 479)
   leaf: 0x1000099 16777369 (14: nrow: 478 rrow: 478)
   leaf: 0x100009a 16777370 (15: nrow: 479 rrow: 479)
   leaf: 0x100009b 16777371 (16: nrow: 479 rrow: 479)
   leaf: 0x100009c 16777372 (17: nrow: 479 rrow: 479)
   leaf: 0x100009d 16777373 (18: nrow: 476 rrow: 476)
   leaf: 0x100009e 16777374 (19: nrow: 449 rrow: 449)
   leaf: 0x100009f 16777375 (20: nrow: 449 rrow: 449)
   leaf: 0x10000e8 16777448 (21: nrow: 449 rrow: 449)
   leaf: 0x10000e9 16777449 (22: nrow: 449 rrow: 449)
   leaf: 0x10000ea 16777450 (23: nrow: 449 rrow: 449)==>nrow表示该索引数据块的总的行数
   leaf: 0x10000eb 16777451 (24: nrow: 449 rrow: 449)==>rrow表示该索引数据块中实际存在有效行数
   leaf: 0x10000ec 16777452 (25: nrow: 449 rrow: 449)
   leaf: 0x10000ed 16777453 (26: nrow: 448 rrow: 448)
   leaf: 0x10000ee 16777454 (27: nrow: 173 rrow: 173)
----- end tree dump



---根据十进位地址来获取文件号和块号
SQL> select dbms_utility.data_block_address_file(16777353) as f,
  2  dbms_utility.data_block_address_block(16777353) as b
  3  from dual;
         F          B
---------- ----------
         4        137

alter system dump datafile 4 block 137;



branch block与leaf block有common和additional information。

--- branch block dump information
Block header dump:  0x01000089
 Object id on Block? Y
 seg/obj: 0x52de  csc: 0x00.ba2a454  itc: 1  flg: -  typ: 2 - INDEX
     fsl: 0  fnx: 0x0 ver: 0x01
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0xffff.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.0ba2a454
Branch block dump
=================
header address 47193300015684=0x2aec0be21a44
kdxcolev 1===>块级别，0是leaf block，>0是branch block，在b-tree里，最大是23，即索引最大高度是24，
KDXCOLEV Flags = - - -
kdxcolok 0===>用来标记一个事务在块上的 block ITL lock，这样其他事物就不能尝试操作此block。当且仅当ITL被hold。
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y===>0x00->v7 btree or branch block;0x10->IOT b-tree;0x20->key compression;0x40->IOT number nested key cols>0;0x80->v8 block
kdxconco 2==>索引的列数
kdxcosdc 0==>索引结构(split或deleted)改变次数
kdxconro 28==>此块的row数目
kdxcofbo 84=0x54==>free space的起始offset
kdxcofeo 7796=0x1e74==>free space的结束offset
kdxcoavs 7712==>在此块上有效的free space
kdxbrlmc 16777354=0x100008a===>指向最左边的child block
kdxbrsno 0===>定义insert or deleter row时的slot修改信息
kdxbrbksz 8056==>?
kdxbr2urrc 0==>?
row#0[8047] dba: 16777355=0x100008b
col 0; len 3; (3):  c2 06 1d
col 1; TERM
row#1[8038] dba: 16777356=0x100008c
col 0; len 3; (3):  c2 0c 0f
col 1; TERM
row#2[8029] dba: 16777357=0x100008d
col 0; len 3; (3):  c2 11 0a
col 1; TERM
row#3[8020] dba: 16777358=0x100008e
col 0; len 3; (3):  c2 15 58
col 1; TERM
row#4[8011] dba: 16777359=0x100008f
col 0; len 3; (3):  c2 1a 43
col 1; TERM
row#5[8002] dba: 16777360=0x1000090
col 0; len 3; (3):  c2 1f 2e
col 1; TERM
row#6[7993] dba: 16777361=0x1000091
.........


---leaf block dump information
Block header dump:  0x010000ec
 Object id on Block? Y
 seg/obj: 0x52de  csc: 0x00.ba2a454  itc: 2  flg: -  typ: 2 - INDEX
     fsl: 0  fnx: 0x0 ver: 0x01
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x02   0xffff.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.0ba2a454
Leaf block dump
===============
header address 47199146199644=0x2aed6857ca5c
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 449
kdxcofbo 934=0x3a6
kdxcofeo 1750=0x6d6
kdxcoavs 816=====>结合branch和leaf块的信息对比，可知，以此为分割点，上面部分为common，而下面的部分为additional。
kdxlespl 0===>space held by unlocked split holes
kdxlende 0===>被删除索引记录数
kdxlenxt 16777453=0x10000ed====>下一个连接数据块地址
kdxleprv 16777451=0x10000eb===>上一个连接数据块地址
kdxledsz 0====>bytes in rowid data,0btyes表示是non—unique,6bytes表示unique index，如果是global则是10bytes
kdxlebksz 8032==>?
row#0[8018] flag: ------, lock: 0, len=14
col 0; len 4; (4):  c3 02 1d 17
col 1; len 6; (6):  01 00 02 20 00 03
row#1[8004] flag: ------, lock: 0, len=14
col 0; len 4; (4):  c3 02 1d 18
col 1; len 6; (6):  01 00 02 20 00 04
row#2[7990] flag: ------, lock: 0, len=14
col 0; len 4; (4):  c3 02 1d 19
col 1; len 6; (6):  01 00 02 20 00 05
row#3[7976] flag: ------, lock: 0, len=14
col 0; len 4; (4):  c3 02 1d 1a
col 1; len 6; (6):  01 00 02 20 00 06
row#4[7962] flag: ------, lock: 0, len=14
.........


---insert/delete/update对index block的影响
insert：
根据算法会搜索到索引block里的leaf block，
如果这个leaf block有free space，则就将数据插入到leaf block里，并且在branch block记录条目
如果这个leaf block没有free space，则就split leaf，最坏的可能branch或者root也要做split，然后将记录写入到新的leaf block里，并且这新的leaf block会有部分原有块的相关信息。
delete：
一条row被删除，记录将会从leaf block去删去，来释放空间容纳后来的条目。
即使leaf bock里只有一条信息，也是tree的一部分；因此only entries that belong in it positionally can be accommodated.
一个leaf block被完全清空，它才会被放到free list里
update:
删除旧的值，然后插入新的值

---index相关trace events；
10224"index block split/delete trace"
10607"trace index rowid partition scan"
10233"skip corrupted blocks on index operations"==>only skipped index range scans
10608"trace create bitmap index"
10606"trace parallel create index"
10211"index block checking";