Oracle internal之---Index internal1

前段时间研究Oracle internal,因此放弃了很多进外企的机会现在分享下,ITpub的空间太小了,全部贴不出来都,看能不能改进下= =!

 

 

 

 

Root Block                           S (root)                                          --&gtLevel 2
                                  /    \
                                 /      \
Branch Blocks                  D/E       U (Branch Block)                              --&gtLevel 1
                               / | \     |\
                              /  |  \    | \
Leaf Blocks                AUS  DEN ENG SCO USA(Leaf Blocks)                           --&gtLevel 0(5条线对应5个Leaf Blocks),AUS,DEN,ENG,SCO,USA为每个Leaf Block的头记录Leaf Block row#1,BEL,CAN和AUS是同一个Leaf Block分别为row#2,row#3;SPA为SCO所在的Leaf Block的row#2
                           BEL          SPA                                            --&gt树在C中表现为链表的形式(带指针)
                           CAN

 

------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------Dump B_Tree Index(41629行)--------------------------------------------------------------------------------
select o.object_id,i.index_name,i.table_name,i.index_type from user_objects o,user_indexes i where o.object_name=i.index_name and i.table_name='OBJ_ID'
 OBJECT_ID INDEX_NAME                     TABLE_NAME                     INDEX_TYPE
---------- ------------------------------ ------------------------------ ---------------------------
     43834 PK_ID                          OBJ_ID                         NORMAL
    
SQL> select dump(object_id,16) from obj_id;
..................
Typ=2 Len=2: c1,64
Typ=2 Len=2: c2,2
Typ=2 Len=3: c2,2,2
Typ=2 Len=3: c2,2,3
Typ=2 Len=3: c2,2,4
...................
-----------------------------------------------------------------------------&gt没有删除增加之前 
----- begin tree dump
branch: 0x340000c 54525964 (0: nrow: 86, level: 1)                                 --&gtBranch DBA(可以dump),block_id=0x34000c(=54526964),格式父块指针0x340000c+本块的DBA:54525964,在这里对于Branch 块父块就是它本身;nrow代表有number of rows,86行)
   leaf: 0x340000d 54525965 (-1: nrow: 520 rrow: 520)                              --&gtLeaf Block Row#,父块0x340000d(=54525964)+DBA(54535965)
   leaf: 0x340000e 54525966 (0: nrow: 513 rrow: 513)                          

   leaf: 0x3400010 54525968 (2: nrow: 513 rrow: 513)
   leaf: 0x3400011 54525969 (3: nrow: 513 rrow: 513)
   leaf: 0x3400012 54525970 (4: nrow: 513 rrow: 513)
   leaf: 0x3400013 54525971 (5: nrow: 513 rrow: 513)
   leaf: 0x3400014 54525972 (6: nrow: 513 rrow: 513)
   leaf: 0x3400015 54525973 (7: nrow: 513 rrow: 513)
 ..........................................................
   leaf: 0x3400027 54525991 (24: nrow: 479 rrow: 479)
   leaf: 0x3400028 54525992 (25: nrow: 479 rrow: 479)
   leaf: 0x340002a 54525994 (26: nrow: 479 rrow: 479)
   leaf: 0x340002b 54525995 (27: nrow: 479 rrow: 479)
.............................................
   leaf: 0x3400064 54526052 (81: nrow: 479 rrow: 479)
   leaf: 0x3400065 54526053 (82: nrow: 479 rrow: 479)
   leaf: 0x3400066 54526054 (83: nrow: 479 rrow: 479)
   leaf: 0x3400067 54526055 (84: nrow: 280 rrow: 280)
----- end tree dump
---------------------------------------------------------------------------------------------------------------Delete,Insert 数据后
----- begin tree dump
branch: 0x340000c 54525964 (0: nrow: 86, level: 1)
   leaf: 0x340000d 54525965 (-1: nrow: 520 rrow: 520)
   leaf: 0x340000e 54525966 (0: nrow: 513 rrow: 513)
   leaf: 0x340000f 54525967 (1: nrow: 513 rrow: 513)
   leaf: 0x3400010 54525968 (2: nrow: 513 rrow: 513)
   leaf: 0x3400011 54525969 (3: nrow: 513 rrow: 513)
   leaf: 0x3400012 54525970 (4: nrow: 513 rrow: 513)
   leaf: 0x3400013 54525971 (5: nrow: 513 rrow: 513)
   leaf: 0x3400014 54525972 (6: nrow: 513 rrow: 344)                                         --&gt注意现在rrow由513减少到344(测试是删除了object_id>4000 object_id<5000的,于是在传统的B_tree index的集中式出现记录Gap)
   leaf: 0x3400016 54525974 (8: nrow: 513 rrow: 221)                                         --&gt注意该Leaf Block块rrow已经由513变为221
   leaf: 0x3400017 54525975 (9: nrow: 513 rrow: 513)
   leaf: 0x3400018 54525976 (10: nrow: 513 rrow: 513)
   leaf: 0x340001a 54525978 (11: nrow: 513 rrow: 513)
   leaf: 0x340001b 54525979 (12: nrow: 513 rrow: 513)
   leaf: 0x340001c 54525980 (13: nrow: 513 rrow: 513)
   ............................................
 ..........................................
   leaf: 0x340004b 54526027 (57: nrow: 479 rrow: 479)
   leaf: 0x340004c 54526028 (58: nrow: 479 rrow: 479)
   leaf: 0x340004d 54526029 (59: nrow: 479 rrow: 479)
   leaf: 0x340004e 54526030 (60: nrow: 479 rrow: 479)
   leaf: 0x340004f 54526031 (61: nrow: 478 rrow: 478)
   leaf: 0x3400050 54526032 (62: nrow: 479 rrow: 479)
   leaf: 0x3400051 54526033 (63: nrow: 479 rrow: 479)
   leaf: 0x3400052 54526034 (64: nrow: 479 rrow: 479)
...........................................................
   leaf: 0x340005c 54526044 (73: nrow: 479 rrow: 479)
   leaf: 0x340005d 54526045 (74: nrow: 479 rrow: 479)
   leaf: 0x340005e 54526046 (75: nrow: 478 rrow: 478)
   leaf: 0x340005f 54526047 (76: nrow: 479 rrow: 479)
   leaf: 0x3400060 54526048 (77: nrow: 479 rrow: 479)
   leaf: 0x3400061 54526049 (78: nrow: 479 rrow: 479)
   leaf: 0x3400062 54526050 (79: nrow: 479 rrow: 479)
   leaf: 0x3400063 54526051 (80: nrow: 479 rrow: 479)
   leaf: 0x3400064 54526052 (81: nrow: 479 rrow: 479)
   leaf: 0x3400065 54526053 (82: nrow: 479 rrow: 479)
   leaf: 0x3400066 54526054 (83: nrow: 479 rrow: 479)
   leaf: 0x3400067 54526055 (84: nrow: 285 rrow: 285)                                               --&gt测试添加了5条记录就在末尾的Leaf Block中追加5条记录,280变成285

----- end tree dump---------                                                                           --&gt测试说明传统的B_Tree索引是排序后按照顺序添加删除的,这样就是按照初始排序规则存储的,当一个块存储满了(75%Block_size)才会继续写下个块

------dump index file block-------------------------------------------------------------------------------------------------------------------------------------------------
SQL> alter index pk_id rebuild tablespace indx;
SQL> select file_id,tablespace_name from dba_data_files where tablespace_name='INDX';                   --&gtindex只有一个索引pk_id
 
   FILE_ID TABLESPACE_NAME
---------- ------------------------------
        13 INDX
alter system dump datafile 1 block min 1 max 100                                                 --&gt1000是估计的,可以算大概索引有多少(这里就100个block)
                                                                                    估算: pk_id={avgdump(object_id)+rowid(10bytes)}*41629/8192(index_block_size)=(3+10)*5.08166503=66       --&gt大约86个
                                                                                      DBA_INDEXES
                                                                                    SQL> select i.blevel,i.leaf_blocks from user_indexes i where i.index_name='PK_ID';
                                                                                       BLEVEL LEAF_BLOCKS
                                                                                      ---------- -----------
                                                                                         1          86
                                                                                        
-------Start dump data blocks tsn: 14 file#: 13 minblk 1 maxblk 100----------------------------------
Block 1 (file header) not dumped: use dump file header command
buffer tsn: 14 rdba: 0x03400002 (13/2)
scn: 0x0000.002ac9e3 seq: 0x02 flg: 0x04 tail: 0xc9e31d02
frmt: 0x02 chkval: 0x47e9 type: 0x1d=KTFB Bitmapped File Space Header
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0CC2B400 to 0x0CC2D400
......
        Repeat 504 times
CC2D3F0 00000000 00000000 00000000 C9E31D02  [................]
File Space Header Block:
Header Control:                                                                            --&gtfile header
RelFno: 13, Unit: 8, Size: 1280, Flag: 9
AutoExtend: YES, Increment: 160, MaxSize: 4194302                                          --&gt自动扩展
Initial Area: 7, Tail: 1280, First: 12, Free: 147
Deallocation scn: 0.0
Header Opcode:
Save: No Pending Op
buffer tsn: 14 rdba: 0x03400003 (13/3)
scn: 0x0000.002ac9e3 seq: 0x01 flg: 0x04 tail: 0xc9e31e01                                  --&gtSCN号
frmt: 0x02 chkval: 0x4374 type: 0x1e=KTFB Bitmapped File Space Bitmap
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0CC2B400 to 0x0CC2D400
..........
  File Space Bitmap Block:                                                                 --&gtbinmap块bitmap管理表空间
BitMap Control:
RelFno: 13, BeginBlock: 9, Flag: 0, First: 12, Free: 63476                                 --&gtbegin block 9
.............
buffer tsn: 14 rdba: 0x03400004 (13/4)
scn: 0x0000.002ac902 seq: 0x01 flg: 0x00 tail: 0xc9021e01
frmt: 0x02 chkval: 0x0000 type: 0x1e=KTFB Bitmapped File Space Bitmap
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0CC2B400 to 0x0CC2D400
CC2B400 0000A21E 03400004 002AC902 00010000  [......@...*.....]
CC2B410 00000000 0000000D 0007C009 00000000  [................]
CC2B420 00000000 0000F800 00000000 00000000  [................]
CC2B430 00000000 00000000 00000000 00000000  [................]
        Repeat 507 times
CC2D3F0 00000000 00000000 00000000 C9021E01  [................]
File Space Bitmap Block:
BitMap Control:
RelFno: 13, BeginBlock: 507913, Flag: 0, First: 0, Free: 63488
...........
buffer tsn: 14 rdba: 0x03400005 (13/5)
scn: 0x0000.002ac904 seq: 0x01 flg: 0x00 tail: 0xc9041e01
frmt: 0x02 chkval: 0x0000 type: 0x1e=KTFB Bitmapped File Space Bitmap
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0CC2B400 to 0x0CC2D400
CC2B400 0000A21E 03400005 002AC904 00010000  [......@...*.....]
CC2B410 00000000 0000000D 000F8009 00000000  [................]
CC2B420 00000000 0000F800 00000000 00000000  [................]
CC2B430 00000000 00000000 00000000 00000000  [................]
        Repeat 507 times
CC2D3F0 00000000 00000000 00000000 C9041E01  [................]
File Space Bitmap Block:
BitMap Control:
RelFno: 13, BeginBlock: 1015817, Flag: 0, First: 0, Free: 63488
...........
buffer tsn: 14 rdba: 0x03400006 (13/6)
scn: 0x0000.002ac906 seq: 0x01 flg: 0x00 tail: 0xc9061e01
frmt: 0x02 chkval: 0x0000 type: 0x1e=KTFB Bitmapped File Space Bitmap
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0CC2B400 to 0x0CC2D400
CC2B400 0000A21E 03400006 002AC906 00010000  [......@...*.....]
CC2B410 00000000 0000000D 00174009 00000000  [.........@......]
CC2B420 00000000 0000F800 00000000 00000000  [................]
CC2B430 00000000 00000000 00000000 00000000  [................]
        Repeat 507 times
CC2D3F0 00000000 00000000 00000000 C9061E01  [................]
File Space Bitmap Block:
BitMap Control:
RelFno: 13, BeginBlock: 1523721, Flag: 0, First: 0, Free: 63488
.....................
buffer tsn: 14 rdba: 0x03400009 (13/9)
scn: 0x0000.002ac9e5 seq: 0x03 flg: 0x04 tail: 0xc9e52003
frmt: 0x02 chkval: 0x2d30 type: 0x20=FIRST LEVEL BITMAP BLOCK
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0CC2B400 to 0x0CC2D400
...................
Dump of First Level Bitmap Block                                                                                    --&gt出现first bitmap block
 --------------------------------
   nbits : 2 nranges: 2         parent dba:  0x0340000a   poffset: 0    
   unformatted: 0       total: 16        first useful block: 3     
   owning instance : 1
   instance ownership changed at 11/04/2009 10:40:40
   Last successful Search 11/04/2009 10:40:40                                                                       --&gt上一次访问的时间
   Freeness Status:  nf1 0      nf2 1      nf3 0      nf4 0     
 
   Extent Map Block Offset: 4294967295
   First free datablock : 3     
   Bitmap block lock opcode 0
   Locker xid:     :  0x0000.000.00000000
   Inc #: 0 Objd: 43835
  --------------------------------------------------------
  DBA Ranges :
  --------------------------------------------------------
   0x03400009  Length: 8      Offset: 0     
   0x03400011  Length: 8      Offset: 8     
 
   0:Metadata   1:Metadata   2:Metadata   3:25-50% free
   4:FULL   5:FULL   6:FULL   7:FULL
   8:FULL   9:FULL   10:FULL   11:FULL
   12:FULL   13:FULL   14:FULL   15:FULL
  --------------------------------------------------------
buffer tsn: 14 rdba: 0x0340000a (13/10)
scn: 0x0000.002ac9e5 seq: 0x05 flg: 0x04 tail: 0xc9e52105
frmt: 0x02 chkval: 0x2c22 type: 0x21=SECOND LEVEL BITMAP BLOCK
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0CC2B400 to 0x0CC2D400
....................
Dump of Second Level Bitmap Block                                                                                       --&gtsecond bitmap block
   number: 6       nfree: 2       ffree: 0      pdba:     0x0340000b
   Inc #: 0 Objd: 43835
  opcode:0
 xid:                                                                                                                  --&gtxid
  L1 Ranges :
  --------------------------------------------------------
   0x03400009  Free: 3 Inst: 1
   0x03400019  Free: 1 Inst: 1
   0x03400029  Free: 1 Inst: 1
   0x03400039  Free: 1 Inst: 1
   0x03400049  Free: 1 Inst: 1
   0x03400059  Free: 5 Inst: 1
 
  --------------------------------------------------------
buffer tsn: 14 rdba: 0x0340000b (13/11)
scn: 0x0000.002ac9e5 seq: 0x03 flg: 0x04 tail: 0xc9e52303
frmt: 0x02 chkval: 0x03e7 type: 0x23=PAGETABLE SEGMENT HEADER
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0CC2B400 to 0x0CC2D400
................
Extent Control Header                                                                                               --&gtExtent Header控制头
  -----------------------------------------------------------------
  Extent Header:: spare1: 0      spare2: 0      #extents: 12     #blocks: 96   
                  last map  0x00000000  #maps: 0      offset: 2716 
      Highwater::  0x03400068  ext#: 11     blk#: 7      ext size: 8    
  #blocks in seg. hdr's freelists: 0    
  #blocks below: 95   
  mapblk  0x00000000  offset: 11   
                   Unlocked
  --------------------------------------------------------
  Low HighWater Mark :                                                                                             --&gtLow HWK
      Highwater::  0x03400068  ext#: 11     blk#: 7      ext size: 8                                               --&gtHWK
  #blocks in seg. hdr's freelists: 0    
  #blocks below: 95   
  mapblk  0x00000000  offset: 11   
  Level 1 BMB for High HWM block: 0x03400059                                                                       --&gtLevel 1 BMB of High HWK
  Level 1 BMB for Low HWM block: 0x03400059                                                                        --&gtLevel 1 BMB of Low HWK
  --------------------------------------------------------                         --&gtSegment type(2是索引?)
  Segment Type: 2 nl2: 1      blksz: 8192   fbsz: 0     
  L2 Array start offset:  0x00001434
  First Level 3 BMB:  0x00000000
  L2 Hint for inserts:  0x0340000a
  Last Level 1 BMB:  0x03400059
  Last Level II BMB:  0x0340000a
  Last Level III BMB:  0x00000000
     Map Header:: next  0x00000000  #extents: 12   obj#: 43835  flag: 0x10000000
  Inc # 0
  Extent Map                                                                                                        --&gtExtent Map
  -----------------------------------------------------------------
   0x03400009  length: 8    
   0x03400011  length: 8    
   0x03400019  length: 8    
   0x03400021  length: 8    
   0x03400029  length: 8    
   0x03400031  length: 8    
   0x03400039  length: 8    
   0x03400041  length: 8    
   0x03400049  length: 8    
   0x03400051  length: 8    
   0x03400059  length: 8    
   0x03400061  length: 8    
 
  Auxillary Map                                                                                                   --&gtAuxiliary Map
  --------------------------------------------------------
   Extent 0     :  L1 dba:  0x03400009 Data dba:  0x0340000c
   Extent 1     :  L1 dba:  0x03400009 Data dba:  0x03400011
   Extent 2     :  L1 dba:  0x03400019 Data dba:  0x0340001a
   Extent 3     :  L1 dba:  0x03400019 Data dba:  0x03400021
   Extent 4     :  L1 dba:  0x03400029 Data dba:  0x0340002a
   Extent 5     :  L1 dba:  0x03400029 Data dba:  0x03400031
   Extent 6     :  L1 dba:  0x03400039 Data dba:  0x0340003a
   Extent 7     :  L1 dba:  0x03400039 Data dba:  0x03400041
   Extent 8     :  L1 dba:  0x03400049 Data dba:  0x0340004a
   Extent 9     :  L1 dba:  0x03400049 Data dba:  0x03400051
   Extent 10    :  L1 dba:  0x03400059 Data dba:  0x0340005a
   Extent 11    :  L1 dba:  0x03400059 Data dba:  0x03400061
  --------------------------------------------------------
 
   Second Level Bitmap block DBAs                                                         --&gtSecond bitmap DBAs
   --------------------------------------------------------
   DBA 1:   0x0340000a
 
buffer tsn: 14 rdba: 0x0340000c (13/12)
scn: 0x0000.002ac9e5 seq: 0x01 flg: 0x04 tail: 0xc9e50601
frmt: 0x02 chkval: 0x9a2a type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0CC2B400 to 0x0CC2D400
...........................................
Block header dump:  0x0340000c                                                                   --Block Comman Header
 Object id on Block? Y
 seg/obj: 0xab3b  csc: 0x00.2ac9c9  itc: 1  flg: E  typ: 2 - INDEX
     brn: 0  bdba: 0x3400009 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc                         --Branch只有一个ITL(?Root Block?)
0x01   0xffff.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.002ac9c9
 
Branch block dump                                                                                              --&gtBranch block Dump
=================
header address 214086732=0xcc2b44c
kdxcolev 1
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: pcode=0: iot flags=--- is converted=Y
kdxconco 1
kdxcosdc 0
kdxconro 85
kdxcofbo 198=0xc6
kdxcofeo 7309=0x1c8d
kdxcoavs 7111
kdxbrlmc 54525965=0x340000d
kdxbrsno 0
kdxbrbksz 8056
kdxbr2urrc 7
row#0[8048] dba: 54525966=0x340000e                                                                           --&gt与treedump DBA,BLOCK_ID一致
col 0; len 3; (3):  c2 06 30
row#1[8040] dba: 54525967=0x340000f
col 0; len 3; (3):  c2 0b 51
row#2[8032] dba: 54525968=0x3400010
col 0; len 3; (3):  c2 10 5e
row#3[8024] dba: 54525969=0x3400011
col 0; len 3; (3):  c2 16 07
row#4[8016] dba: 54525970=0x3400012
col 0; len 3; (3):  c2 1b 14
row#5[8008] dba: 54525971=0x3400013
col 0; len 3; (3):  c2 20 21
row#6[8000] dba: 54525972=0x3400014
..................................................................................
col 0; len 4; (4):  c3 03 24 22
row#47[7642] dba: 54526016=0x3400040
col 0; len 4; (4):  c3 03 29 0d
row#48[7633] dba: 54526017=0x3400041
col 0; len 4; (4):  c3 03 2d 5b
row#49[7624] dba: 54526018=0x3400042
col 0; len 4; (4):  c3 03 32 46
row#50[7615] dba: 54526019=0x3400043
col 0; len 4; (4):  c3 03 37 31
row#51[7606] dba: 54526020=0x3400044
col 0; len 4; (4):  c3 03 3c 1c
row#52[7597] dba: 54526021=0x3400045
col 0; len 4; (4):  c3 03 41 07
row#53[7588] dba: 54526022=0x3400046
col 0; len 4; (4):  c3 03 45 55
row#54[7579] dba: 54526023=0x3400047
col 0; len 4; (4):  c3 03 4a 40
row#55[7570] dba: 54526024=0x3400048
col 0; len 4; (4):  c3 03 4f 2b
................................
row#83[7318] dba: 54526054=0x3400066
col 0; len 4; (4):  c3 05 0e 14
row#84[7309] dba: 54526055=0x3400067                                                                      84#和dba_belevel相差2
col 0; len 4; (4):  c3 05 13 46
----- end of branch block dump -----                                                                        --&gtBranch block end
......................................
..........................
        Repeat 1 times
CC2D3F0 00000000 00000000 00000000 C9CA0602  [................]
Block header dump:  0x0340000d                                                            --Block Comman Header
 Object id on Block? Y
 seg/obj: 0xab3b  csc: 0x00.2ac9c9  itc: 2  flg: E  typ: 2 - INDEX
     brn: 0  bdba: 0x3400009 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc                  --ITL
0x01   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x02   0xffff.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.002ac9c9
 
Leaf block dump                                                                                         --&gtLeaf Block Dump
===============
header address 214086756=0xcc2b464                                                        --Index Header
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: pcode=0: iot flags=--- is converted=Y
kdxconco 1
kdxcosdc 0
kdxconro 520
kdxcofbo 1076=0x434
kdxcofeo 1895=0x767
kdxcoavs 819
kdxlespl 0
kdxlende 0
kdxlenxt 54525966=0x340000e
kdxleprv 0=0x0
kdxledsz 6
kdxlebksz 8032
row#0[8021] flag: ------, lock: 0, len=11, data:(6):  01 00 00 2c 00 2d
col 0; len 2; (2):  c1 03
row#1[8010] flag: ------, lock: 0, len=11, data:(6):  01 00 00 2c 00 05
col 0; len 2; (2):  c1 04
row#2[7999] flag: ------, lock: 0, len=11, data:(6):  01 00 00 2c 00 2e
col 0; len 2; (2):  c1 05
row#3[7988] flag: ------, lock: 0, len=11, data:(6):  01 00 00 2c 00 19
col 0; len 2; (2):  c1 06
..........................
col 0; len 2; (2):  c1 0c
row#10[7911] flag: ------, lock: 0, len=11, data:(6):  01 00 00 2c 00 25
col 0; len 2; (2):  c1 0d
row#11[7900] flag: ------, lock: 0, len=11, data:(6):  01 00 00 2c 00 0b
col 0; len 2; (2):  c1 0e
row#12[7889] flag: ------, lock: 0, len=11, data:(6):  01 00 00 2c 00 13
col 0; len 2; (2):  c1 0f
row#13[7878] flag: ------, lock: 0, len=11, data:(6):  01 00 00 2c 00 03
......................
row#518[1907] flag: ------, lock: 0, len=12, data:(6):  01 00 00 2d 00 ed                                       --&gtdump(obj_id)大概2-3之间,加10个rowid的字节
col 0; len 3; (3):  c2 06 2d
row#519[1895] flag: ------, lock: 0, len=12, data:(6):  01 00 00 2d 00 ee                                       --&gtrow#=519与treedump 相应DBA对应level block的nrows匹配
col 0; len 3; (3):  c2 06 2e                                                                                 
----- end of leaf block dump -----                                                                              --&gtLeaf Block Dump end
.......................                                                                                         --&gtselect 520*12/8192 from dual(0.76171875),大概在75%的使用空间
.........
Block header dump:  0x0340000e
 Object id on Block? Y
 seg/obj: 0xab3b  csc: 0x00.2ac9c9  itc: 2  flg: E  typ: 2 - INDEX
     brn: 0  bdba: 0x3400009 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 
 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.002ac9c9
 
Leaf block dump
===============                                                                                               --&gtLeaf Block dump
header address 214086756=0xcc2b464
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: pcode=0: iot flags=--- is converted=Y
kdxconco 1
kdxcosdc 0
kdxconro 513
kdxcofbo 1062=0x426
kdxcofeo 1881=0x759
kdxcoavs 819
kdxlespl 0
kdxlende 0
kdxlenxt 54525967=0x340000f
kdxleprv 54525965=0x340000d
kdxledsz 6
kdxlebksz 8032
row#0[8020] flag: ------, lock: 0, len=12, data:(6):  01 00 00 2d 00 ef
col 0; len 3; (3):  c2 06 30
row#1[8008] flag: ------, lock: 0, len=12, data:(6):  01 00 00 2d 00 f0
col 0; len 3; (3):  c2 06 31
row#2[7996] flag: ------, lock: 0, len=12, data:(6):  01 00 00 2d 00 f1
col 0; len 3; (3):  c2 06 32
row#3[7984] flag: ------, lock: 0, len=12, data:(6):  01 00 00 2d 00 f2
col 0; len 3; (3):  c2 06 33
row#4[7972] flag: ------, lock: 0, len=12, data:(6):  01 00 00 2d 00 f3
col 0; len 3; (3):  c2 06 34
row#5[7960] flag: ------, lock: 0, len=12, data:(6):  01 00 00 2d 00 f4
col 0; len 3; (3):  c2 06 35
row#6[7948] flag: ------, lock: 0, len=12, data:(6):  01 00 00 2d 00 f5
col 0; len 3; (3):  c2 06 36
....................................
........................................
row#507[1941] flag: ------, lock: 0, len=12, data:(6):  01 00 00 30 00 3d
col 0; len 3; (3):  c2 0b 4b
row#508[1929] flag: ------, lock: 0, len=12, data:(6):  01 00 00 30 00 3e
col 0; len 3; (3):  c2 0b 4c
row#509[1917] flag: ------, lock: 0, len=12, data:(6):  01 00 00 30 00 3f
col 0; len 3; (3):  c2 0b 4d
row#510[1905] flag: ------, lock: 0, len=12, data:(6):  01 00 00 30 00 40
col 0; len 3; (3):  c2 0b 4e
row#511[1893] flag: ------, lock: 0, len=12, data:(6):  01 00 00 30 00 41
col 0; len 3; (3):  c2 0b 4f
row#512[1881] flag: ------, lock: 0, len=12, data:(6):  01 00 00 30 00 42                                      --&gtrow#512,
col 0; len 3; (3):  c2 0b 50
----- end of leaf block dump -----                                                                             --&gtLeaf Block Dump end
...............................
.............................
Block header dump:  0x03400063
 Object id on Block? Y
 seg/obj: 0xab3b  csc: 0x00.2ac9c9  itc: 2  flg: E  typ: 2 - INDEX
     brn: 1  bdba: 0x3400059 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 
 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.002ac9c9
 
Leaf block dump                                                                                                --&gtLeaf Block Dump
===============
header address 214086756=0xcc2b464
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: pcode=0: iot flags=--- is converted=Y
kdxconco 1
kdxcosdc 0
kdxconro 479
kdxcofbo 994=0x3e2
kdxcofeo 1810=0x712
kdxcoavs 816
kdxlespl 0
kdxlende 0
kdxlenxt 54526052=0x3400064
kdxleprv 54526050=0x3400062
kdxledsz 6
kdxlebksz 8032
row#0[8019] flag: ------, lock: 0, len=13, data:(6):  01 00 02 82 00 4c
col 0; len 4; (4):  c3 04 63 10
row#1[8006] flag: ------, lock: 0, len=13, data:(6):  01 00 02 82 00 4d
col 0; len 4; (4):  c3 04 63 11
row#2[7993] flag: ------, lock: 0, len=13, data:(6):  01 00 02 82 00 4e
col 0; len 4; (4):  c3 04 63 12
row#3[7980] flag: ------, lock: 0, len=13, data:(6):  01 00 02 82 00 4f
col 0; len 4; (4):  c3 04 63 13
row#4[7967] flag: ------, lock: 0, len=13, data:(6):  01 00 02 82 00 50
.......................
row#477[1823] flag: ------, lock: 0, len=13, data:(6):  01 00 02 85 00 5c
col 0; len 4; (4):  c3 05 03 5d
row#478[1810] flag: ------, lock: 0, len=13, data:(6):  01 00 02 85 00 5d                             --&gtrow#478
col 0; len 4; (4):  c3 05 03 5e
----- end of leaf block dump -----                                                                     --&gtLeaf Block dump end

-----------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------
---------------Reverse Index(更新数据在不同的leaf block块) --------------------------------------------------------------------------------------------------------------------
create table obj_id_rev as select object_id,object_name,status from obj;
create unique index pk_id_rev on obj_id_rev(object_id) reverse; 
analyze table obj_id_rev compute statistics;
SQL> select object_id from user_objects where object_name='PK_ID_REV';
 OBJECT_ID
----------
     43918
 alter session set events 'immediate trace name treedump level 43918';
----- begin tree dump
branch: 0x100082c 16779308 (0: nrow: 86, level: 1)
   leaf: 0x100082d 16779309 (-1: nrow: 486 rrow: 486)
   leaf: 0x100082e 16779310 (0: nrow: 486 rrow: 486)
   leaf: 0x100082f 16779311 (1: nrow: 486 rrow: 486)
   leaf: 0x1000830 16779312 (2: nrow: 486 rrow: 486)
   leaf: 0x1000909 16779529 (3: nrow: 486 rrow: 486)
   leaf: 0x100090a 16779530 (4: nrow: 486 rrow: 486)
   leaf: 0x100090b 16779531 (5: nrow: 486 rrow: 486)
   leaf: 0x100090c 16779532 (6: nrow: 486 rrow: 486)
   leaf: 0x100090d 16779533 (7: nrow: 486 rrow: 486)
   leaf: 0x100090e 16779534 (8: nrow: 486 rrow: 486)
   ................
   leaf: 0x1000923 16779555 (27: nrow: 486 rrow: 486)
.................................
   leaf: 0x1000c1c 16780316 (81: nrow: 486 rrow: 486)
   leaf: 0x1000c1d 16780317 (82: nrow: 486 rrow: 486)
   leaf: 0x1000c1e 16780318 (83: nrow: 486 rrow: 486)
   leaf: 0x1000c1f 16780319 (84: nrow: 319 rrow: 319)
----- end tree dump
--------------------------------------------------------------------------------------------------------------------------&gt没做更改之前的   
--------------------------------------------------------------------------------------------------------------------------&gtInsert,Detele一部分数据
--------------------------------------------------------------------------------------------------------------------------
branch: 0x100082c 16779308 (0: nrow: 86, level: 1)
   leaf: 0x100082d 16779309 (-1: nrow: 486 rrow: 478)                             

   leaf: 0x100082e 16779310 (0: nrow: 486 rrow: 476)
......................................................
   leaf: 0x100090e 16779534 (8: nrow: 486 rrow: 469)
   leaf: 0x100090f 16779535 (9: nrow: 486 rrow: 473)
   leaf: 0x1000910 16779536 (10: nrow: 486 rrow: 476)
   leaf: 0x1000912 16779538 (11: nrow: 486 rrow: 477)
   leaf: 0x1000913 16779539 (12: nrow: 486 rrow: 476)
   leaf: 0x1000914 16779540 (13: nrow: 486 rrow: 476)
 ..............................
   leaf: 0x1000c1d 16780317 (82: nrow: 486 rrow: 477)
   leaf: 0x1000c1e 16780318 (83: nrow: 486 rrow: 476)
   leaf: 0x1000c1f 16780319 (84: nrow: 319 rrow: 311)
----- end tree dump

 

 

 

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15072844/viewspace-620287/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/15072844/viewspace-620287/