[20121015]探索索引-學習bbed.txt
[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......... .@.............
<48 bytes per line>
--對比上面可以發現,這裡的順序是排序的.
--另外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內指定的位置對應的值才是有序的.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-746457/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Elasticsearch 學習索引Elasticsearch索引
- MySQL學習 - 索引MySql索引
- MySQL學習之索引MySql索引
- 索引學習二—blevel索引
- 索引學習筆記索引筆記
- 戰隼的學習探索
- Css基礎學習—索引CSS索引
- PostgreSQL學習手冊(索引)SQL索引
- Bootstrap~學習筆記索引boot筆記索引
- MySQL學習筆記：組合索引-索引下推MySql筆記索引
- MYSQL學習(三) --索引詳解MySql索引
- MySQL學習筆記：索引失效MySql筆記索引
- MySql 學習筆記二：索引MySql筆記索引
- 分割槽索引學習筆記索引筆記
- ITPUB 知識索引--ASM 學習索引ASM
- 索引內部結構探索索引
- MongoDB學習之豐富的索引MongoDB索引
- InnoDB學習（七）之索引結構索引
- InnoDB學習（八）之 聚簇索引索引
- 學習資料庫索引機制資料庫索引
- Python點陣圖索引學習Python索引
- 關於不可見索引的學習索引
- oracle學習筆記——檢視、索引Oracle筆記索引
- MySQL探索(一):B-Tree索引MySql索引
- React Native框架探索學習筆記React Native框架筆記
- 關於mongodb的學習與探索二MongoDB
- 弱監督學習在醫學影像中的探索
- 再一次學習 MySQL 索引MySql索引
- MySQL優化學習筆記之索引MySql優化筆記索引
- MySQL 學習之索引篇和查詢MySql索引
- CUUG筆記 ORACLE索引學習筆記筆記Oracle索引
- 學習Oracle的索引、表的儲存Oracle索引
- [20160529]windows下使用bbed.txtWindows
- 【MongoDB學習筆記】MongoDB索引那點事MongoDB筆記索引
- mysql學習11：第六章:索引MySql索引
- iOS 基礎知識學習目錄索引iOS索引
- 【學習】SQL基礎-018-索引SQL索引
- 一個程式設計師的數學學習探索之旅程式設計師