logical rowid的结构
Posted: December 12, 2011 | Author: Cui Hua | Filed under: Oracle | Tags: logical rowid | Leave a comment »先说一下结论,然后详细描述我得到这个结论的过程:
logical rowid的结构为:
length(logical rowid) || 2 || length(physical guess dba) || physical guess dba || length(primary key) || primary key || ending flag
IOT上non unique索引里存的logical rowid的结构为:
04或者05(05表示已被删掉) || 00或者02 || length(index key) || index key || length(primary key) || primary key ||
IOT上unique索引里存的logical rowid的结构为:
04或者05(05表示已被删掉)|| 00或者02 || length(index key) || index key ||
先来看一个IOT的overflow的情况:
SQL> create table t3 (id number, c1 varchar2(1),c2 char(2000), c3 char(2000), c4 char(2000),constraint pk_t2_id primary key (id))
2 organization index tablespace testtbs
3 pctthreshold 5
4 overflow tablespace testtbs;
Table created
SQL> insert into t3 VALUES (1,’A’, ‘A’, ‘A’, ‘A’);
1 row inserted
SQL> insert into t3 VALUES (2,’B’, ‘B’, ‘B’, ‘B’);
1 row inserted
SQL> commit;
Commit complete
SQL> select id,rowid,dump(rowid,16) from t3;
ID ROWID DUMP(ROWID,16)
———- ——————————- ——————————————————————————–
1 *BCFAACoCwQL+ Typ=208 Len=10: 2,4,21,40,0,
2 *BCFAACoCwQP+ Typ=208 Len=10: 2,4,21,40,0,
我们来看ID为1的这条记录的rowid,这里dump出来是值是Typ=208 Len=10: 2,4,21,40,0,2a,2,c1,2,fe,这里我从左往右依次解释每个byte的含义:
这里type是208,即urowid
length为10个byte
2表示是logical rowid
4表示physical guess记录的dba的长度
紧接着的四个byte 21 40 00
SQL> exec sys.cdba(‘
The file is 133
The block is 42
PL/SQL procedure successfully completed
接着的2表示primary key的长度
紧接着的两个byte C1 02是primary key的值,C1 02也就是1
SQL> select dump(1,16) from dual;
DUMP(1,16)
—————–
Typ=2 Len=2: c1,2
最后的一个byte是fe,即254,估计是一个ending flag。
我们现在dump 133_42这个block:
row#0[8016] flag: K—-, lock: 2
col 0; len 2; (2): c1 02
tl: 11 fb: –H-F— lb: 0x0 cc: 1
nrid: 0x21400012.0
col 0: [ 1]
Dump of memory from 0x0000000110275FBB to 0x0000000110275FBC
110275FB0 00000141 […A]
row#1[8000] flag: K—-, lock: 2
col 0; len 2; (2): c1 03
tl: 11 fb: –H-F— lb: 0x0 cc: 1
nrid: 0x21400013.0
col 0: [ 1]
Dump of memory from 0x0000000110275FAB to 0x0000000110275FAC
110275FA0 00000142 […B]
这里我们可以看到oracle把表t3中的c2、c3、c4给存在0x21400012和0x21400013里了,这符合我们建t3时指定pctthreshold为5%的要求。
现在我们重建表t3:
SQL> create table t3 (id number, c1 varchar2(1),c2 char(2000),constraint pk_t2_id primary key (id))
2 organization index tablespace testtbs;
Table created
SQL> insert into t3 VALUES (1,’A’, ‘A’);
1 row inserted
SQL> insert into t3 VALUES (2,’B’, ‘B’);
1 row inserted
SQL> insert into t3 VALUES (3,’C’, ‘C’);
1 row inserted
SQL> insert into t3 VALUES (4,’D’, ‘D’);
1 row inserted
SQL> commit;
Commit complete
SQL> select id,rowid,dump(rowid,16) from t3;
ID ROWID DUMP(ROWID,16)
———- ——————————- ——————————————————————————–
1 *BCFAABMCwQL+ Typ=208 Len=10: 2,4,21,40,0,13,2,c1,2,fe
2 *BCFAABMCwQP+ Typ=208 Len=10: 2,4,21,40,0,13,2,c1,3,fe
3 *BCFAABMCwQT+ Typ=208 Len=10: 2,4,21,40,0,13,2,c1,4,fe
4 *BCFAABQCwQX+ Typ=208 Len=10: 2,4,21,40,0,14,2,c1,5,fe
按照重建后t3的定义,现在一个block里最多能能存3条记录,所以我们可以从dump的结果里看到ID为4的那条记录的已经被存在了0x21400014里。
现在我们在t3上对c1建一个索引:
SQL> create index idx_t3_c1 on t3(c1) tablespace testtbs;
Index created
对上述index做一个treedump:
—– begin tree dump
leaf: 0x
—– end tree dump
再dump 0x2140002a:
row#1[8002] flag: K—-, lock: 0
col 0; len 1; (1): 42
col 1; len 2; (2): c1 03
tl: 8 fb: –H-FL– lb: 0x0 cc: 1
col 0: [ 4]
Dump of memory from 0x0000000110275FA9 to 0x0000000110275FAD
110275FA0 04214000 13040001 [.!@…..]
按道理讲,IOT上的索引应该会存索引键值、physical guess和primary key,这里我们来验证一下索引键值为2的那条记录是如何存的:
因为testtbs是MSSM的LMT,且itc为2,所以这里的offset为:
8002+68+(2-1)*24=8094
BBED> set offset 8094
OFFSET 8094
BBED> dump
File: /dras20/astca/test01.dbf (133)
Block: 42 Offsets: 8094 to 8191 Dba:0x
————————————————————————
04000142
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 0000afee
0601
<32 bytes per line>
从dump结果里可以看到索引键值为2的那条记录的索引键值是42(也就是B),primary key是C103(也就是2),physical guess是21400013,这和我们dump rowid的结果一致。
好了,我们现在再来构造一个physical guess为stale的情况:
索引键值为2的那条记录的physical guess是21 400013,我们现在将其改成21400014,按照oracle文档中的说法,我这里即使改了也没关系的,oracle当检索到stale的physical guess的时候,会再根据primary key再去扫描一遍IOT,从而得到正确的值。
在改之前的结果是:
SQL> select id,substr(c2,1,1) from t3 where c1=’B’;
ID SUBSTR(C2,1,1)
———- ————–
2 B
先shutdown,再改:
BBED> set offset 8094
OFFSET 8094
BBED> dump
File: /dras20/astca/test01.dbf (133)
Block: 42 Offsets: 8094 to 8191 Dba:0x
————————————————————————
04000142
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 0000b
0601
<32 bytes per line>
BBED> modify /x 0x
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /dras20/astca/test01.dbf (133)
Block: 42 Offsets: 8094 to 8191 Dba:0x
————————————————————————
04000142
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 0000b
0601
<32 bytes per line>
BBED> sum apply
Check value for File 133, Block 42:
current = 0x
改完了,现在索引键值为2的那条记录的physical guess已经变成了21400014,这已经是stale的了,我们现在再startup上述数据库,然后看一下改过之后的结果:
SQL> explain plan for select id,substr(c2,1,1) from t3 where c1=’B’;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
——————————————————————————–
——————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost |
——————————————————————–
| 0 | SELECT STATEMENT | | 82 | 161K| 3 |
| 1 | INDEX UNIQUE SCAN | PK_T2_ID | 82 | 161K| 1 |
| 2 | INDEX RANGE SCAN | IDX_T3_C1 | 33 | | 1 |
——————————————————————–
Note: cpu costing is off, PLAN_TABLE’ is old version
10 rows selected
SQL> select id,substr(c2,1,1) from t3 where c1=’B’;
ID SUBSTR(C2,1,1)
———- ————–
2 B
SQL> select id,rowid,dump(rowid,16) from t3;
ID ROWID DUMP(ROWID,16)
———- ——————————- ——————————————————————————–
1 *BCFAABMCwQL+ Typ=208 Len=10: 2,4,21,40,0,13,2,c1,2,fe
2 *BCFAABMCwQP+ Typ=208 Len=10: 2,4,21,40,0,13,2,c1,3,fe
3 *BCFAABMCwQT+ Typ=208 Len=10: 2,4,21,40,0,13,2,c1,4,fe
4 *BCFAABQCwQX+ Typ=208 Len=10: 2,4,21,40,0,14,2,c1,5,fe
果然,oracle这里还是得到了正确的结果,也就是说即使是stale的physical guess,这里也没关系。另外我们从dump里可以看到,当你对IOT的rowid做查询的时候,这里的rowid是oracle现算出来的,所以这里ID=2的那条记录的physical guess还是正确的值21400013。
如果IDX_T3_C1是unique index的话,则其logical rowid是这样存的:
BBED> set offset 8094
OFFSET 8094
BBED> dump
File: /dras20/astca/test01.dbf (133)
Block: 42 Offsets: 8094 to 8191 Dba:0x
————————————————————————
04000142
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 0000be77
0601
Recent Comments