mysql 偏移量是什么_数据块内部偏移量的基本计算方法

转载请注明出处：http://blog.csdn.net/guoyjoe/article/details/32715157 BASE的计算方法为： gyj@ZMDB select * from v$type_size where component in (KCB,KTB);COMPONEN TYPE DESCRIPTION TYPE_SIZE-------- -------- --------------------------------

转载请注明出处：http://blog.csdn.net/guoyjoe/article/details/32715157

BASE的计算方法为：

gyj@ZMDB> select * from v$type_size where component in ('KCB','KTB');

COMPONEN TYPE DESCRIPTION TYPE_SIZE

-------- -------- -------------------------------- ----------

KCB KCBH BLOCK COMMON HEADER 20

KTB KTBIT TRANSACTION VARIABLE HEADER 24

KTB KTBBH TRANSACTION FIXED HEADER 48

KTB KTBBH_BS TRANSACTION BLOCK BITMAP SEGMENT 8

1、我们先对ASSM做测试

yj@ZMDB> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

gyj@ZMDB> create tablespace assm datafile '/u01/app/oracle/oradata/zmdb/assm01.dbf' size 50M;

Tablespace created.

gyj@ZMDB> create table gyj_t5(id int,name varchar2(100)) tablespace assm;

Table created.

gyj@ZMDB> insert into gyj_t5 values(1,'AAAAA');

1 row created.

gyj@ZMDB> insert into gyj_t5 values(2,'BBBBB');

1 row created.

gyj@ZMDB> insert into gyj_t5 values(3,'CCCCC');

1 row created.

gyj@ZMDB> COMMIT;

Commit complete.

gyj@ZMDB> alter system flush buffer_cache;

System altered.

gyj@ZMDB> select dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block#,id,name from gyj_t5;

FILE# BLOCK# ID NAME

---------- ---------- ---------- ----------

10 135 1 AAAAA

10 135 2 BBBBB

10 135 3 CCCCC

BBED> set file 10 block 135

FILE# 10

BLOCK# 135

BBED> p kdbr[0]

sb2 kdbr[0] @118 8076

BBED> p *kdbr[0]

rowdata[24]

-----------

ub1 rowdata[24] @8176 0x2c

BBED> x /rnc

rowdata[24] @8176

-----------

flag@8176: 0x2c (KDRHFL, KDRHFF, KDRHFH)

lock@8177: 0x01

cols@8178: 2

col 0[2] @8179: 1

col 1[5] @8182: AAAAA

BBED> p ktbbhict

sb2 ktbbhict @36 2

8176-8076=76+(itc-1) * 24= 76+(2-1)* 24=100

2、我们对MSSM做测试

gyj@ZMDB> create tablespace mssm datafile '/u01/app/oracle/oradata/zmdb/mssm01.dbf' size 50M segment space management manual;

Tablespace created.

gyj@ZMDB> create table gyj_mssm(id int,name varchar2(100)) tablespace mssm;

Table created.

gyj@ZMDB> insert into gyj_mssm values(4,'DDDDD');

1 row created.

gyj@ZMDB> insert into gyj_mssm values(5,'EEEEE');

1 row created.

gyj@ZMDB> insert into gyj_mssm values(6,'FFFFF');

1 row created.

gyj@ZMDB> commit;

Commit complete.

gyj@ZMDB> col name for a20

gyj@ZMDB> select dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block#,id,name from gyj_mssm;

FILE# BLOCK# ID NAME

---------- ---------- ---------- --------------------

11 129 4 DDDDD

11 129 5 EEEEE

11 129 6 FFFFF

BBED> set file 11 block 129

FILE# 11

BLOCK# 129

BBED> p kdbr[0]

sb2 kdbr[0] @110 8084

BBED> p *kdbr[0]

rowdata[24]

-----------

ub1 rowdata[24] @8176 0x2c

BBED> x /rnc

rowdata[24] @8176

-----------

flag@8176: 0x2c (KDRHFL, KDRHFF, KDRHFH)

lock@8177: 0x01

cols@8178: 2

col 0[2] @8179: 4

col 1[5] @8182: DDDDD

BBED> p ktbbhict

sb2 ktbbhict @36 2

8176-8084=68+(itc-1) * 24=68+(2-1)*24=92

3、为什么ASSM要比MSSM多了8个字节

************MSSM

BBED> set file 11 block 129

FILE# 11

BLOCK# 129

BBED> map /v

File: /u01/app/oracle/oradata/zmdb/mssm01.dbf (11)

Block: 129 Dba:0x02c00081

------------------------------------------------------------

KTB Data Block (Table/Cluster)

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 kdbh, 14 bytes @92

ub1 kdbhflag @92

sb1 kdbhntab @93

sb2 kdbhnrow @94

sb2 kdbhfrre @96

sb2 kdbhfsbo @98

sb2 kdbhfseo @100

sb2 kdbhavsp @102

sb2 kdbhtosp @104

struct kdbt[1], 4 bytes @106

sb2 kdbtoffs @106

sb2 kdbtnrow @108

sb2 kdbr[3] @110

ub1 freespace[8036] @116

ub1 rowdata[36] @8152

ub4 tailchk @8188

*****************ASSM

BBED> set file 10 block 135

FILE# 10

BLOCK# 135

File: /u01/app/oracle/oradata/zmdb/assm01.dbf (10)

Block: 141 Dba:0x0280008d

------------------------------------------------------------

KTB Data Block (Table/Cluster)

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 kdbh, 14 bytes @100

ub1 kdbhflag @100

sb1 kdbhntab @101

sb2 kdbhnrow @102

sb2 kdbhfrre @104

sb2 kdbhfsbo @106

sb2 kdbhfseo @108

sb2 kdbhavsp @110

sb2 kdbhtosp @112

struct kdbt[1], 4 bytes @114

sb2 kdbtoffs @114

sb2 kdbtnrow @116

sb2 kdbr[3] @118

ub1 freespace[8028] @124

ub1 rowdata[36] @8152

ub4 tailchk @8188

对比

struct kdbh, 14 bytes @92

---ASSM

struct kdbh, 14 bytes @100

本文原创发布php中文网，转载请注明出处，感谢您的尊重！