[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

--這樣正好對上.

[20141008]使用bbed檢視索引結構.txt

相關文章