[20141008]使用bbed查看索引结构.txt
--今天使用bbed查看索引结构,发现一些问题.链接如下:
http://blog.itpub.net/267265/viewspace-1291526/
--那scott.dept表做一个测试看看.
SCOTT@test> @ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
SCOTT@test> select * from dept ;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS1
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 aaa bbb
60 cc aaa
70 aaaa BBBB
7 rows selected.
SCOTT@test> select segment_name,header_file,header_block from dba_segments where owner=user and segment_name='PK_DEPT';
SEGMENT_NAME HEADER_FILE HEADER_BLOCK
-------------------- ----------- ------------
PK_DEPT 4 130
-- pk_dept的root节点在4,131,通过bbed观察:
BBED> set dba 4,131
DBA 0x01000083 (16777347 4,131)
BBED> p kd_off
sb2 kd_off[0] @132 8032
sb2 kd_off[1] @134 0
sb2 kd_off[2] @136 8021
sb2 kd_off[3] @138 8010
sb2 kd_off[4] @140 7999
sb2 kd_off[5] @142 7988
sb2 kd_off[6] @144 7955
--kd_off保存的信息如何确定偏移位置呢?
p *kd_off[6]
x /rn
p *kd_off[5]
x /rn
p *kd_off[4]
x /rn
p *kd_off[3]
x /rn
p *kd_off[2]
x /rn
p *kd_off[1]
x /rn
p *kd_off[0]
x /rn
p *kd_off[6]
rowdata[37]
-----------
ub1 rowdata[37] @8055 0x00
x /rn
rowdata[37] @8055
-----------
flag@8055: 0x00 (NONE)
lock@8056: 0x00
keydata[6]: 0x01 0x00 0x05 0x84 0x00 0x00
data key:
col 0[2] @8064: 50
--索引因为是主键索引,rowid信息在前面,并且没有长度指示器.
p *kd_off[5]
rowdata[70]
-----------
ub1 rowdata[70] @8088 0x00
x /rn
rowdata[70] @8088
-----------
flag@8088: 0x00 (NONE)
lock@8089: 0x00
keydata[6]: 0x01 0x00 0x05 0x83 0x00 0x04
data key:
col 0[2] @8097: 40
p *kd_off[4]
rowdata[81]
-----------
ub1 rowdata[81] @8099 0x00
x /rn
rowdata[81] @8099
-----------
flag@8099: 0x00 (NONE)
lock@8100: 0x00
keydata[6]: 0x01 0x00 0x05 0x83 0x00 0x03
data key:
col 0[2] @8108: 30
p *kd_off[3]
rowdata[92]
-----------
ub1 rowdata[92] @8110 0x00
x /rn
rowdata[92] @8110
-----------
flag@8110: 0x00 (NONE)
lock@8111: 0x00
keydata[6]: 0x01 0x00 0x05 0x83 0x00 0x02
data key:
col 0[2] @8119: 20
p *kd_off[2]
rowdata[103]
------------
ub1 rowdata[103] @8121 0x00
x /rn
rowdata[103] @8121
------------
flag@8121: 0x00 (NONE)
lock@8122: 0x00
keydata[6]: 0x01 0x00 0x05 0x83 0x00 0x01
data key: col 0[2] @8130: 10
p *kd_off[1]
kdxle.kdxlexco.kdxcolev
-----------------------
ub1 kdxcolev @100 0x00
x /rn
kdxle.kdxlexco.kdxcolev @100
-----------------------
BBED-00210: no row at this offset
p *kd_off[0]
pad
---
ub1 pad @8132 0x00
BBED> x /rn
pad @8132
---
flag@8132: 0x00 (NONE)
lock@8133: 0x00
keydata[6]: 0x00 0x00 0x00 0x00 0x00 0x00
data key:
BBED-00210: no row at this offset
--后面2个出现错误,后面的偏移如何确定呢?对应的是deptno=60,deptno=70的索引.
--想起以前写的一篇"[20111223]索引键值在B tree索引块中的顺序.txt"
http://blog.itpub.net/267265/viewspace-713847/
--索引的存储是块内无序,块间有序.从前面的输出可以看出kd_off[3]开始的偏移指向的键值(10)是有序的.可以猜测在kd_off[6]后面应
--该还有两个.前面的kd_off[0],kd_off[1]或许是其他信息.
BBED> dump /v offset 146 count 2
File: /u01/app/oracle11g/oradata/test/users01.dbf (4)
Block: 131 Offsets: 146 to 147 Dba:0x01000083
---------------------------------------------------------------------------------------------------------------------------------------------------------------
fd1e l ..
BBED> dump /v offset 148 count 2
File: /u01/app/oracle11g/oradata/test/users01.dbf (4)
Block: 131 Offsets: 148 to 149 Dba:0x01000083
---------------------------------------------------------------------------------------------------------------------------------------------------------------
f21e l ..
--对应16进制如下:(顺序要颠倒1下)
SCOTT@test> @16to10 1ef2
16 to 10 DEC
------------
7922
SCOTT@test> @16to10 1efd
16 to 10 DEC
------------
7933
BBED> set offset 8022
OFFSET 8022
BBED> x /rn
rowdata[4] @8022
----------
flag@8022: 0x00 (NONE)
lock@8023: 0x00
keydata[6]: 0x01 0x00 0x05 0x85 0x00 0x01
data key:
col 0[2] @8031: 70
BBED> set offset 8033
OFFSET 8033
BBED> x /rn
rowdata[15] @8033
-----------
flag@8033: 0x00 (NONE)
lock@8034: 0x00
keydata[6]: 0x01 0x00 0x05 0x85 0x00 0x00
data key:
col 0[2] @8042: 60
--这样正好对上.