[20121015]探索索引-学习bbed.txt
参考链接：http://www.adellera.it/blog/2009/05/24/order-keys-inside-index-blocks/
1.探索索引
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> create table t (x varchar2(10)) tablespace test;
SQL> create index i_t_x on t(x) tablespace test;
随机插入如下记录:
insert into t values('000000');
insert into t values('777777');
insert into t values('111111');
insert into t values('666666');
insert into t values('222222');
insert into t values('555555');
insert into t values('333333');
insert into t values('444444');
commit ;
2.看看对应的索引块的位置:
SQL> select object_id,data_object_id from dba_objects where wner=USER and object_name='I_T_X';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
114882 114882
SQL> select object_id,data_object_id from dba_objects where wner=USER and object_name='T';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
114881 114881
SQL> select header_file,header_block from dba_segments where wner=USER and segment_name='I_T_X';
HEADER_FILE HEADER_BLOCK
----------- ------------
8 146
SQL> alter system checkpoint;
System altered.
--保证数据信息写到磁盘.
SQL> column dump(rowid,16) format a50
SQL> select dump(rowid,16) ,dbms_rowid.rowid_relative_fno(rowid) rfile#,dbms_rowid.ROWID_BLOCK_NUMBER(rowid) block#,dbms_rowid.ROWID_ROW_NUMBER(rowid) row#,t.* from scott.t;
DUMP(ROWID,16) RFILE# BLOCK# ROW# X
-------------------------------------------------- ---------- ---------- ---------- ----------
Typ=69 Len=10: 0,1,c0,c1,2,0,0,8f,0,0 8 143 0 000000
Typ=69 Len=10: 0,1,c0,c1,2,0,0,8f,0,1 8 143 1 777777
Typ=69 Len=10: 0,1,c0,c1,2,0,0,8f,0,2 8 143 2 111111
Typ=69 Len=10: 0,1,c0,c1,2,0,0,8f,0,3 8 143 3 666666
Typ=69 Len=10: 0,1,c0,c1,2,0,0,8f,0,4 8 143 4 222222
Typ=69 Len=10: 0,1,c0,c1,2,0,0,8f,0,5 8 143 5 555555
Typ=69 Len=10: 0,1,c0,c1,2,0,0,8f,0,6 8 143 6 333333
Typ=69 Len=10: 0,1,c0,c1,2,0,0,8f,0,7 8 143 7 444444
8 rows selected.
$ bc
bc 1.06
Copyright 1991-1994, 1997, 1998, 2000 Free Software Foundation, Inc.
This is free software with ABSOLUTELY NO WARRANTY.
For details type `warranty'.
ibase=16
1C0C1
114881
--rowid前面几个字节对应表T的DATA_OBJECT_ID.
3.设置bbed
在.bashrc加入函数:
rlbbedro()
{
cd /home/oracle11g/bbed
rlwrap -s 9999 -c -r -i -f /usr/local/share/rlwrap/bbed /u01/app/oracle11g/product/11.2.0/db_1/bin/bbed parfile=bbedreadonly.par cmdfile=cmd.par
}
$ cat bbed/bbedreadonly.par
blocksize=8192
listfile=/home/oracle11g/bbed/filelist.txt
mode=browse
PASSWORD=blockedit
$ cat bbed/cmd.par
set count 8192
set width 210
--安全起见,采用browse模式.
BBED> set dba 8,147
DBA 0x02000093 (33554579 8,147)
--
BBED> map /v
File: /u01/app/oracle11g/oradata/test/test01.dbf (8)
Block: 147 Dba:0x02000093
------------------------------------------------------------
KTB Data Block (Index Leaf)
struct kcbh, 20 bytes @0
ub1 type_kcbh @0
ub1 frmt_kcbh @1
ub1 spare1_kcbh @2
ub1 spare2_kcbh @3
ub4 rdba_kcbh @4
ub4 bas_kcbh @8
ub2 wrp_kcbh @12
ub1 seq_kcbh @14
ub1 flg_kcbh @15
ub2 chkval_kcbh @16
ub2 spare3_kcbh @18
struct ktbbh, 72 bytes @20
ub1 ktbbhtyp @20
union ktbbhsid, 4 bytes @24
struct ktbbhcsc, 8 bytes @28
sb2 ktbbhict @36
ub1 ktbbhflg @38
ub1 ktbbhfsl @39
ub4 ktbbhfnx @40
struct ktbbhitl[2], 48 bytes @44
struct kdxle, 32 bytes @100
struct kdxlexco, 16 bytes @100
sb2 kdxlespl @116
sb2 kdxlende @118
ub4 kdxlenxt @120
ub4 kdxleprv @124
ub1 kdxledsz @128
ub1 kdxleflg @129
sb2 kd_off[8] @132
ub1 freespace[7852] @148
ub1 rowdata[128] @8000
ub4 tailchk @8188
BBED> p kd_off
sb2 kd_off[0] @132 8032
sb2 kd_off[1] @134 0
sb2 kd_off[2] @136 8016
sb2 kd_off[3] @138 7984
sb2 kd_off[4] @140 7952
sb2 kd_off[5] @142 7920
sb2 kd_off[6] @144 7904
sb2 kd_off[7] @146 7936
--最小的位置是:
sb2 kd_off[6] @144 7904
BBED> p *kd_off[6]
rowdata[4]
----------
ub1 rowdata[4] @8004 0x00
BBED> x /8rcx
rowdata[4] @8004
----------
flag@8004: 0x00 (NONE)
lock@8005: 0x02
data key:
col 0[6] @8007: 444444
col 1[6] @8014: 0x02 0x00 0x00 0x8f 0x00 0x07
rowdata[20] @8020
-----------
flag@8020: 0x00 (NONE)
lock@8021: 0x02
data key:
col 0[6] @8023: 333333
col 1[6] @8030: 0x02 0x00 0x00 0x8f 0x00 0x06
rowdata[36] @8036
-----------
flag@8036: 0x00 (NONE)
lock@8037: 0x02
data key:
col 0[6] @8039: 555555
col 1[6] @8046: 0x02 0x00 0x00 0x8f 0x00 0x05
rowdata[52] @8052
-----------
flag@8052: 0x00 (NONE)
lock@8053: 0x02
data key:
col 0[6] @8055: 222222
col 1[6] @8062: 0x02 0x00 0x00 0x8f 0x00 0x04
rowdata[68] @8068
-----------
flag@8068: 0x00 (NONE)
lock@8069: 0x02
data key:
col 0[6] @8071: 666666
col 1[6] @8078: 0x02 0x00 0x00 0x8f 0x00 0x03
rowdata[84] @8084
-----------
flag@8084: 0x00 (NONE)
lock@8085: 0x02
data key:
col 0[6] @8087: 111111
col 1[6] @8094: 0x02 0x00 0x00 0x8f 0x00 0x02
rowdata[100] @8100
------------
flag@8100: 0x00 (NONE)
lock@8101: 0x02
data key:
col 0[6] @8103: 777777
col 1[6] @8110: 0x02 0x00 0x00 0x8f 0x00 0x01
rowdata[116] @8116
------------
flag@8116: 0x00 (NONE)
lock@8117: 0x02
data key:
col 0[6] @8119: 000000
col 1[6] @8126: 0x02 0x00 0x00 0x8f 0x00 0x00
--可以发现(倒着看),索引也是从底部插入的,与数据的插入顺序一致.
--而且rowid也对应.
4.看看kd_off结构:
sb2 kd_off[0] @132 8032
sb2 kd_off[1] @134 0
sb2 kd_off[2] @136 8016
sb2 kd_off[3] @138 7984
sb2 kd_off[4] @140 7952
sb2 kd_off[5] @142 7920
sb2 kd_off[6] @144 7904
sb2 kd_off[7] @146 7936
BBED> dump /v offset 132 count 32
File: /u01/app/oracle11g/oradata/test/test01.dbf (8)
Block: 147 Offsets: 132 to 163 Dba:0x02000093
---------------------------------------------------------------------------------------------------------------------------------------------------------------
601f0000 501f301f 101ff01e e01e001f 201f401f 00000000 00000000 00000000 l `...P.0......... .@.............
--对比上面可以发现,这里的顺序是排序的.
--另外11G的bbed可能存在一些问题,kd_off 存在偏差,遗漏了1f20(7968),1f40(8000).
BBED> p *kd_off[2]
rowdata[116]
------------
ub1 rowdata[116] @8116 0x00
BBED> x /rcx
rowdata[116] @8116
------------
flag@8116: 0x00 (NONE)
lock@8117: 0x02
data key:
col 0[6] @8119: 000000
col 1[6] @8126: 0x02 0x00 0x00 0x8f 0x00 0x00
BBED> p *kd_off[3]
rowdata[84]
-----------
ub1 rowdata[84] @8084 0x00
BBED> x /rcx
rowdata[84] @8084
-----------
flag@8084: 0x00 (NONE)
lock@8085: 0x02
data key:
col 0[6] @8087: 111111
col 1[6] @8094: 0x02 0x00 0x00 0x8f 0x00 0x02
BBED> p *kd_off[4]
rowdata[52]
-----------
ub1 rowdata[52] @8052 0x00
BBED> x /rcx
rowdata[52] @8052
-----------
flag@8052: 0x00 (NONE)
lock@8053: 0x02
data key:
col 0[6] @8055: 222222
col 1[6] @8062: 0x02 0x00 0x00 0x8f 0x00 0x04
BBED> p *kd_off[5]
rowdata[20]
-----------
ub1 rowdata[20] @8020 0x00
BBED> x /rcx
rowdata[20] @8020
-----------
flag@8020: 0x00 (NONE)
lock@8021: 0x02
data key:
col 0[6] @8023: 333333
col 1[6] @8030: 0x02 0x00 0x00 0x8f 0x00 0x06
BBED> p *kd_off[6]
rowdata[4]
----------
ub1 rowdata[4] @8004 0x00
BBED> x /rcx
rowdata[4] @8004
----------
flag@8004: 0x00 (NONE)
lock@8005: 0x02
data key:
col 0[6] @8007: 444444
col 1[6] @8014: 0x02 0x00 0x00 0x8f 0x00 0x07
BBED> p *kd_off[7]
rowdata[36]
-----------
ub1 rowdata[36] @8036 0x00
BBED> x /rcx
rowdata[36] @8036
-----------
flag@8036: 0x00 (NONE)
lock@8037: 0x02
data key:
col 0[6] @8039: 555555
col 1[6] @8046: 0x02 0x00 0x00 0x8f 0x00 0x05
--7968+100
BBED> set offset 8068
OFFSET 8068
BBED> x /rcx
rowdata[68] @8068
-----------
flag@8068: 0x00 (NONE)
lock@8069: 0x02
data key:
col 0[6] @8071: 666666
col 1[6] @8078: 0x02 0x00 0x00 0x8f 0x00 0x03
--8000+100
BBED> set offset 8100
OFFSET 8100
BBED> x /rcx
rowdata[100] @8100
------------
flag@8100: 0x00 (NONE)
lock@8101: 0x02
data key:
col 0[6] @8103: 777777
col 1[6] @8110: 0x02 0x00 0x00 0x8f 0x00 0x01
5.总结:
可以发现块内索引值是无序的,kd_off内指定的位置对应的值才是有序的.