logical rowid的结构

先说一下结论,然后详细描述我得到这个结论的过程:

logical rowid的结构为:

length(logical rowid) || 2 || length(physical guess dba) || physical guess dba || length(primary key) || primary key || ending flag

 

IOTnon unique索引里存的logical rowid的结构为:

04或者0505表示已被删掉) || 00或者02 || length(index key) || index key || length(primary key) || primary key || 2c || 00或者01(取决于ITC) || 01 || length(physical guess dba) || physical guess dba

 

IOTunique索引里存的logical rowid的结构为:

04或者0505表示已被删掉)|| 00或者02 || length(index key) || index key || 2c || 00或者01(取决于ITC) || 01 || length(primary key+physical guess)+1 || length(primary key) || primary key || physical guess dba

 

 

先来看一个IOToverflow的情况:

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,2a,2,c1,2,fe

         2 *BCFAACoCwQP+                 Typ=208 Len=10: 2,4,21,40,0,2a,2,c1,3,fe

 

我们来看ID1的这条记录的rowid,这里dump出来是值是Typ=208 Len=10: 2,4,21,40,0,2a,2,c1,2,fe这里我从左往右依次解释每个byte的含义:

这里type208,即urowid

length10byte

2表示是logical rowid

4表示physical guess记录的dba的长度

紧接着的四个byte 21 40 00 2aphysical guess记录的dba

SQL> exec sys.cdba(‘2140002a‘,’H’);

 

The file is 133

The block is 42

 

PL/SQL procedure successfully completed

 

接着的2表示primary key的长度

紧接着的两个byte C1 02primary key的值,C1 02也就是1

SQL> select dump(1,16) from dual;

 

DUMP(1,16)

—————–

Typ=2 Len=2: c1,2

 

最后的一个bytefe,即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中的c2c3c4给存在0x214000120x21400013里了,这符合我们建t3时指定pctthreshold5%的要求。

 

现在我们重建表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的结果里看到ID4的那条记录的已经被存在了0x21400014里。

 

现在我们在t3上对c1建一个索引:

SQL> create index idx_t3_c1 on t3(c1) tablespace testtbs;

 

Index created

 

对上述index做一个treedump

—– begin tree dump

leaf: 0x2140002a 557842474 (0: nrow: 4 rrow: 4)

—– 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 guessprimary key,这里我们来验证一下索引键值为2的那条记录是如何存的:

因为testtbsMSSMLMT,且itc2,所以这里的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:0x2140002a

————————————————————————

 04000142 02c1032c 00010421 40001304 00014102 c1022c00 01042140 00130000

 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 keyC103(也就是2),physical guess21400013,这和我们dump rowid的结果一致。

 

好了,我们现在再来构造一个physical guessstale的情况:

索引键值为2的那条记录的physical guess21 400013,我们现在将其改成21400014,按照oracle文档中的说法,我这里即使改了也没关系的,oracle当检索到stalephysical 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:0x2140002a

————————————————————————

 04000142 02c1032c 00010421 40001305 00014102 c1022c00 01042140 00130000

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 0000b7c4

 0601

 

 <32 bytes per line>

 

BBED> modify /x 0x0400014202c1032c00010421400014  

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:0x2140002a

————————————————————————

 04000142 02c1032c 00010421 40001405 00014102 c1022c00 01042140 00130000

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 0000b7c4

 0601

 

 <32 bytes per line>

 

BBED> sum apply

Check value for File 133, Block 42:

current = 0x725f, required = 0x725f

 

改完了,现在索引键值为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这里还是得到了正确的结果,也就是说即使是stalephysical guess,这里也没关系。另外我们从dump里可以看到,当你对IOTrowid做查询的时候,这里的rowidoracle现算出来的,所以这里ID=2的那条记录的physical guess还是正确的值21400013

 

如果IDX_T3_C1unique index的话,则其logical rowid是这样存的:

BBED> set offset 8094

        OFFSET          8094

 

BBED> dump

 File: /dras20/astca/test01.dbf (133)

 Block: 42               Offsets: 8094 to 8191           Dba:0x2140002a

————————————————————————

 04000142 2c000107 02c10321 40001304 0001412c 00010702 c1022140 00130000

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 0000be77

 0601

 



Leave a Reply

Your email address will not be published. Required fields are marked *