分享程序员开发的那些事...
更新时间：2022-09-13 10:58:24
[20150720]为什么8K数据块Hakan Factor=736.txt
--前几天被别人问及这个问题，还真不好回答。
--仔细思考，我觉得与行迁移有关，行迁移发生时，rowid不会变化。数据信息被移动另外的块，在块内保留一个指针。
--也就是讲最小这条记录仅仅包含一个rowid指针。
--还是通过例子来说明情况：
1.建立测试环境:
SCOTT@test> @&r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- ----------------------------------------------------------------
x86_64/Linux 2.4.xx 10.2.0.4.0 Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
SCOTT@test> create table test (id varchar2(100)) pctfree 0 tablespace mssm ;
Table created.
SCOTT@test> insert into test select null from dba_objects where rownum<=1000;
1000 rows created.
SCOTT@test> commit ;
Commit complete.
SCOTT@test> select rowid,test.* from test where rownum<=1;
ROWID ID
------------------ -------
AAAQZYAAGAAAAAKAAA
SCOTT@test> @&r/lookup_rowid AAAQZYAAGAAAAAKAAA
OBJECT FILE BLOCK ROW DBA TEXT
------------ ------------ ------------ ------------ -------------------- ----------------------------------------
67160 6 10 0 6,10 alter system dump datafile 6 block 10 ;
SCOTT@test> select count(*) from test where rowid between 'AAAQZYAAGAAAAAKAAA' and 'AAAQZYAAGAAAAAKDDD';
COUNT(*)
------------
734
SCOTT@test> select spare1 from sys.tab$ where obj#=67160;
SPARE1
------------
736
-- 1块 仅仅734 条记录，比规定最大少3条记录。(注意spare1=736,也就是最大行号736,实际上就是每块最大插入737条记录(行号从0开始).)
SCOTT@test> alter system checkpoint;
System altered.
2.通过bbed观察：
BBED> set dba 6,10
DBA 0x0180000a (25165834 6,10)
BBED> p *kdbr[0]
rowdata[1437]
-------------
ub1 rowdata[1437] @7423 0x2c
BBED> x /rcccc
rowdata[1437] @7423
-------------
flag@7423: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@7424: 0x01
cols@7425: 0
BBED> x /rc
rowdata[1437] @7423
-------------
flag@7423: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@7424: 0x01
cols@7425: 0
--可以发现NULL并没有记录，一条记录仅仅占用3个字节。 2c 01 00
BBED> map
File: /mnt/ramdisk/test/mssm01.dbf (6)
Block: 10 Dba:0x0180000a
------------------------------------------------------------
KTB Data Block (Table/Cluster)
struct kcbh, 20 bytes @0
struct ktbbh, 72 bytes @20
struct kdbh, 14 bytes @92
struct kdbt[1], 4 bytes @106
sb2 kdbr[734] @110
ub1 freespace[4408] @1578
ub1 rowdata[2202] @5986
ub4 tailchk @8188
-- freespace = 4408 , 几乎一半的空间剩下。2202/3=734,说明信息对上。
BBED> p kdbr[733]
sb2 kdbr[733] @1576 6563
--说明块头部包括行目录，已经占用1578字节。
--这样留给数据部分是：
8192-1578-4=6610 (扣除尾部tailchk4个字节)
6610/734=9.0054495912806539595
6610-734*9=4
--这样计算也就是每条记录最多9个字节。每条记录前面已经占用3个字节，这样仅仅剩下6个字节。(仅仅够保留rowid)
--做一个大胆的猜测，如果734条记录全部发生行迁移，rowid指针必须保留，这样正好够。
--换一句话讲假设修改如下：
update test set id='1' where rowid='AAAQZYAAGAAAAAKAAA';
--仅仅1条记录，在这样的情况下(注意记录前面有1个字节保存字符串长度)就不会发生行迁移,但是如果修改如下：
update test set id='123456' where rowid='AAAQZYAAGAAAAAKAAA';
--这样有会发生行迁移，继续测试看看：
3.继续测试：
SCOTT@test> update test set id='1' where rowid='AAAQZYAAGAAAAAKAAA';
1 row updated.
SCOTT@test> commit ;
Commit complete.
SCOTT@test> alter system checkpoint;
System altered.
BBED> set dba 6,10
DBA 0x0180000a (25165834 6,10)
BBED> p *kdbr[0]
rowdata[0]
----------
ub1 rowdata[0] @5981 0x2c
BBED> x /rc
rowdata[0] @5981
----------
flag@5981: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@5982: 0x02
cols@5983: 1
col 0[1] @5984: 1
--没有发生行迁移。
SCOTT@test> update test set id='12' where rowid='AAAQZYAAGAAAAAKAAA';
1 row updated.
SCOTT@test> commit ;
Commit complete.
SCOTT@test> alter system checkpoint;
System altered.
BBED> x /rc
rowdata[0] @5975
----------
flag@5975: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@5976: 0x01
cols@5977: 1
col 0[2] @5978: 12
--没有发生行迁移。
SCOTT@test> update test set id='123' where rowid='AAAQZYAAGAAAAAKAAA';
1 row updated.
SCOTT@test> commit ;
Commit complete.
SCOTT@test> alter system checkpoint;
System altered.
BBED> x /rc
rowdata[0] @5968
----------
flag@5968: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@5969: 0x02
cols@5970: 1
col 0[3] @5971: 123
SCOTT@test> update test set id='1234' where rowid='AAAQZYAAGAAAAAKAAA';
1 row updated.
SCOTT@test> commit ;
Commit complete.
SCOTT@test> alter system checkpoint;
System altered.
BBED> x /rc
rowdata[0] @5960
----------
flag@5960: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@5961: 0x01
cols@5962: 1
col 0[4] @5963: 1234
--继续：
SCOTT@test> update test set id='12345' where rowid='AAAQZYAAGAAAAAKAAA';
1 row updated.
SCOTT@test> commit ;
Commit complete.
SCOTT@test> alter system checkpoint;
System altered.
BBED> x /rc
rowdata[0] @5951
----------
flag@5951: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@5952: 0x02
cols@5953: 1
col 0[5] @5954: 12345
SCOTT@test> update test set id='1234567' where rowid='AAAQZYAAGAAAAAKAAA';
1 row updated.
SCOTT@test> commit ;
Commit complete.
SCOTT@test> alter system checkpoint;
System altered.
--恩，奇怪并没有出现我想象的情况。
BBED> x /rc
rowdata[0] @5930
----------
flag@5930: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@5931: 0x02
cols@5932: 1
col 0[7] @5933: 1234567
4.重来看看：
SCOTT@test> drop table test purge ;
Table dropped.
SCOTT@test> create table test (id varchar2(100)) pctfree 0 tablespace mssm ;
Table created.
SCOTT@test> insert into test select null from dba_objects where rownum<=1000;
1000 rows created.
SCOTT@test> commit ;
Commit complete.
SCOTT@test> select rowid,test.* from test where rownum<=1;
ROWID ID
------------------ ----------------------------------------------------------------------------------------------------
AAAQZdAAGAAAAAKAAA
SCOTT@test> @ &r/lookup_rowid AAAQZdAAGAAAAAKAAA
OBJECT FILE BLOCK ROW DBA TEXT
------------ ------------ ------------ ------------ -------------------- ----------------------------------------
67165 6 10 0 6,10 alter system dump datafile 6 block 10 ;
SCOTT@test> update test set id='12345' where rowid='AAAQZdAAGAAAAAKAAA';
1 row updated.
SCOTT@test> commit ;
Commit complete.
SCOTT@test> alter system checkpoint;
System altered.
BBED> set dba 6,10
DBA 0x0180000a (25165834 6,10)
BBED> p *kdbr[0]
rowdata[0]
----------
ub1 rowdata[0] @5977 0x20
BBED> x /rc
rowdata[0] @5977
----------
flag@5977: 0x20 (KDRHFH)
lock@5978: 0x02
cols@5979: 0
nrid@5980:0x0180000b.10a
--奇怪慢慢增加不行，也许更oracle内部算法有关。bvi观察到 20 02 00 01 80 00 0B 01 0A(9个字节)，注意这里rowid全面没有长度指示器。
5.全部update看看：
SCOTT@test> update test set id='12345' ;
1000 rows updated.
SCOTT@test> commit ;
Commit complete.
SCOTT@test> alter system checkpoint;
System altered.
BBED> map
File: /mnt/ramdisk/test/mssm01.dbf (6)
Block: 10 Dba:0x0180000a
------------------------------------------------------------
KTB Data Block (Table/Cluster)
struct kcbh, 20 bytes @0
struct ktbbh, 72 bytes @20
struct kdbh, 14 bytes @92
struct kdbt[1], 4 bytes @106
sb2 kdbr[734] @110
ub1 freespace[4] @1578
ub1 rowdata[6606] @1582
ub4 tailchk @8188
--这样仅仅剩下4自己。几乎没有剩下的空间。可以发现每条记录都发生了行迁移。
BBED> p *kdbr[1]
rowdata[6588]
-------------
ub1 rowdata[6588] @8170 0x20
BBED> x /rc
rowdata[6588] @8170
-------------
flag@8170: 0x20 (KDRHFH)
lock@8171: 0x01
cols@8172: 0
nrid@8173:0x0180000b.10b
--从以上分析可以推出计算最大行号(Hakan Factor)按照剩余空间/每条记录9字节来计算的。
--因为每个行目录占2个字节。加上每条记录9个字节。
(8192-110-4)=8078/11=734.36
--为什么少了几条呢，我推测：
--9i下建表仅仅1个itl槽，而现在这样有2个itl槽，注ctas建表有3个itl槽。这样多出了24字节。
--这样算下来正好多处2条记录。加起来正好是736，不过还是差1(行号从0开始).
8192-110-4+24=8102
8102/11=736.54
--我测试表空间是SEGMENT SPACE MANAGEMENT MANUAL。如果是SEGMENT SPACE MANAGEMENT AUTO，还要少1条，是733.
--大家可以在自行测试。有机会测试9i就清楚了。