共计 6408 个字符，预计需要花费 17 分钟才能阅读完成。
我们知道索引叶块中保存的内容是 ” 被索引的字段值 +rowid”，我们如何使用这个 rowid 找到对应的数据行？
### 创建测试用表和索引
col segment_name format a40
col object_name format a40
set linesize 80
select table_name,index_name from dba_indexes where table_name=’T1123_1′;
TABLE_NAME INDEX_NAME
—————————— ——————————
T1123_1 IND_T1123_1_OBJID
select object_name,object_id from dba_objects where object_name=’IND_T1123_1_OBJID’;
OBJECT_NAME OBJECT_ID
—————————————- ———-
IND_T1123_1_OBJID 18924
col name format a30
col value format a70
set linesize 120
select name,value from v$diag_info where name=’Default Trace File’;
NAME VALUE
—————————— ———————————————————————-
Default Trace File /u01/app/Oracle/diag/rdbms/mydb/mydb/trace/mydb_ora_6498.trc
###dump 索引结构
alter session set events ‘immediate trace name treedump level 18924’;
branch: 0x1c000bb 29360315 (0: nrow: 19, level: 1)
leaf: 0x1c000bc 29360316 (-1: nrow: 481 rrow: 481)
leaf: 0x1c000bd 29360317 (0: nrow: 478 rrow: 478)
leaf: 0x1c000be 29360318 (1: nrow: 478 rrow: 478)
leaf: 0x1c000bf 29360319 (2: nrow: 478 rrow: 478)
leaf: 0x1c000c0 29360320 (3: nrow: 478 rrow: 478)
leaf: 0x1c000c1 29360321 (4: nrow: 478 rrow: 478)
leaf: 0x1c000c2 29360322 (5: nrow: 478 rrow: 478)
leaf: 0x1c000c3 29360323 (6: nrow: 478 rrow: 478)
leaf: 0x1c000c4 29360324 (7: nrow: 478 rrow: 478)
leaf: 0x1c000c5 29360325 (8: nrow: 478 rrow: 478)
leaf: 0x1c000c6 29360326 (9: nrow: 455 rrow: 455)
leaf: 0x1c000c7 29360327 (10: nrow: 448 rrow: 448)
leaf: 0x1c000c9 29360329 (11: nrow: 448 rrow: 448)
leaf: 0x1c000ca 29360330 (12: nrow: 448 rrow: 448)
leaf: 0x1c000cb 29360331 (13: nrow: 448 rrow: 448)
leaf: 0x1c000cc 29360332 (14: nrow: 448 rrow: 448)
leaf: 0x1c000cd 29360333 (15: nrow: 448 rrow: 448)
leaf: 0x1c000ce 29360334 (16: nrow: 448 rrow: 448)
leaf: 0x1c000cf 29360335 (17: nrow: 438 rrow: 438)
选择其中所在的叶子节点 block：29360318 做 dump
select dbms_utility.data_block_address_file(29360318) fileno,dbms_utility.data_block_address_block(29360318) blkno from dual;
FILENO BLKNO
———- ———-
7 190
alter system dump datafile 7 block 190;
###mydb_ora_6498.trc 内容
header address 140037440318052=0x7f5d01e2aa64
kdxcolev 0
KDXCOLEV Flags = – – –
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=— is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 478
kdxcofbo 992=0x3e0
kdxcofeo 1818=0x71a
kdxcoavs 826
kdxlespl 0
kdxlende 0
kdxlenxt 29360319=0x1c000bf
kdxleprv 29360317=0x1c000bd
kdxledsz 0
kdxlebksz 8032
row#0[8019] flag: ——, lock: 0, len=13
col 0; len 3; (3): c2 14 18
col 1; len 6; (6): 01 40 00 a5 00 16
row#1[8006] flag: ——, lock: 0, len=13
col 0; len 3; (3): c2 14 1a
col 1; len 6; (6): 01 40 00 a5 00 18
row#2[7993] flag: ——, lock: 0, len=13
col 0; len 3; (3): c2 14 1c
col 1; len 6; (6): 01 40 00 a5 00 1a
row#3[7980] flag: ——, lock: 0, len=13
col 0; len 3; (3): c2 14 1e
col 1; len 6; (6): 01 40 00 a5 00 1c
row#4[7967] flag: ——, lock: 0, len=13
col 0; len 3; (3): c2 14 20
col 1; len 6; (6): 01 40 00 a5 00 1e
row#5[7954] flag: ——, lock: 0, len=13
col 0; len 3; (3): c2 14 22
col 1; len 6; (6): 01 40 00 a5 00 20
row#6[7941] flag: ——, lock: 0, len=13
col 0; len 3; (3): c2 14 24
col 1; len 6; (6): 01 40 00 a5 00 22
。。。省略部分内容
选取其中的 row#5，找出键值及对应的 rowid
键值是 ”c2 14 22″、rowid 是 ”01 40 00 a5 00 20″
### 键值转换成实际值
select utl_raw.cast_to_number(replace(‘c2 14 22′,’ ‘)) from dual;
UTL_RAW.CAST_TO_NUMBER(REPLACE(‘C21422’,”))
——————————————–
1933
### 从 rowid 得到 relative_fno、block number、row number
“01 40 00 a5 00 20″ 共 6 个字节，48bit，转换成二进制是
00000001 01000000 00000000 10100101 00000000 00100000
其中 1 -10bit 代表 relative_fno (5)
17-32bit 代表 block number (165)
33-48bit 代表 row number (32)
### 使用 dbms_rowid 将 object_id=1933 这条记录所在行的 rowid 进行转换，以验证��述结果
select dbms_rowid.rowid_relative_fno(rowid) relative_fno,dbms_rowid.rowid_block_number(rowid) blkno,dbms_rowid.rowid_row_number(rowid) row_number from t1123_1 where object_id=1933;
RELATIVE_FNO BLKNO ROW_NUMBER
———— ———- ———-
5 165 32 <— 得到的值与我们上一步计算出的结果一致
alter system dump datafile 5 block 165;
tab 0, row 32, @0xb23
tl: 92 fb: –H-FL– lb: 0x0 cc: 14
col 0: [3] 53 59 53
col 1: [22]
56 5f 24 53 54 52 45 41 4d 53 5f 50 4f 4f 4c 5f 41 44 56 49 43 45
col 2: *NULL*
col 3: [3] c2 14 22 <— 和索引 leaf block 的保存的键值一致
col 4: *NULL*
col 5: [4] 56 49 45 57
col 6: [7] 78 74 0a 08 11 23 2c
col 7: [7] 78 74 0a 08 11 23 2c
col 8: [19] 32 30 31 36 2d 31 30 2d 30 38 3a 31 36 3a 33 34 3a 34 33
col 9: [5] 56 41 4c 49 44
col 10: [1] 4e
col 11: [1] 4e
col 12: [1] 4e
col 13: [2] c1 02
需要注意的是索引叶块里的 rowid 信息，与通过 rowid 伪列输出的 rowid 信息格式稍有不同，前者使用的是 restricted rowid 形式，后者使用的是 extended rowid 格式，较之 restricted rowid 增加了 object number 信息，并且采用了 BASE64 编码。
可以通过 dbms_rowid 这个 package 里的函数在 extended rowid 与 object_id、relative_fno、block_number、row_number 之间相互转换：
###extended rowid => object_id、relative_fno、block_number、row_number
SQL> select rowid from t1123_1 where object_id=1933;
ROWID
——————
AAAEnyAAFAAAAClAAg
set serveroutput on
DECLARE
v_rowid_type NUMBER;
v_OBJECT_NUMBER NUMBER;
v_RELATIVE_FNO NUMBER;
v_BLOCK_NUMBERE_FNO NUMBER;
v_ROW_NUMBER NUMBER;
BEGIN
DBMS_ROWID.rowid_info (rowid_in => ‘AAAEnyAAFAAAAClAAg’,
rowid_type => v_rowid_type,
object_number => v_OBJECT_NUMBER,
relative_fno => v_RELATIVE_FNO,
block_number => v_BLOCK_NUMBERE_FNO,
ROW_NUMBER => v_ROW_NUMBER);
DBMS_OUTPUT.put_line (‘ROWID_TYPE: ‘ || TO_CHAR (v_rowid_type));
DBMS_OUTPUT.put_line (‘OBJECT_NUMBER: ‘ || TO_CHAR (v_OBJECT_NUMBER));
DBMS_OUTPUT.put_line (‘RELATIVE_FNO: ‘ || TO_CHAR (v_RELATIVE_FNO));
DBMS_OUTPUT.put_line (‘BLOCK_NUMBER: ‘ || TO_CHAR (v_BLOCK_NUMBERE_FNO));
DBMS_OUTPUT.put_line (‘ROW_NUMBER: ‘ || TO_CHAR (v_ROW_NUMBER));
END;
/
ROWID_TYPE: 1
OBJECT_NUMBER: 18930 <— 注意这里是表 t1123_1 的 object_id
RELATIVE_FNO: 5
BLOCK_NUMBER: 165
ROW_NUMBER: 32
###object_id、relative_fno、block_number、row_number => extended rowid
select DBMS_ROWID.ROWID_CREATE(rowid_type=>1,object_number=>18930,relative_fno=>5,block_number=>165,row_number=>32) from dual;
DBMS_ROWID.ROWID_C
——————
AAAEnyAAFAAAAClAAg
其实还有一种方法可以佐证 extended rowid 与 restricted rowid 之间的关系：
将本例中的 extended rowid : AAAEnyAAFAAAAClAAg 插入一张空表中，然后 dump 出数据块
create table t1124_1 (f1 rowid) tablespace st1;
insert into t1124_1 values(‘AAAEnyAAFAAAAClAAg’);
select * from t1124_1;
F1
——————
AAAEnyAAFAAAAClAAg
select dbms_rowid.rowid_relative_fno(rowid) rfno,dbms_rowid.rowid_block_number(rowid) blkno from t1124_1;
RFNO BLKNO
———- ———-
7 158
alter system dump datafile 7 block 158;
。。。省略部分内容
block_row_dump:
tab 0, row 0, @0x1f8a
tl: 14 fb: –H-FL– lb: 0x1 cc: 1
col 0: [10] 00 00 49 f2 01 40 00 a5 00 20
end_of_block_dump
End dump data blocks tsn: 11 file#: 7 minblk 158 maxblk 158
可以看到标注红色的部分与最初保存在索引叶块里的 rowid 是一致的(蓝色标注的部分表示 object number，在 restricted rowid 里这部分是没有的)。
更多 Oracle 相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12
本文永久更新链接地址：http://www.linuxidc.com/Linux/2016-11/137542.htm
