[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 <== 与上面保存信息偏移100.以下也是一样.

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 ..
<48 bytes per line>

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 ..
<48 bytes per line>

--对应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

--这样正好对上.