[20150314]256列.txt
--oracle 当1个表超过256列时,要分成几个行片(row pieces),昨天看链接:
https://jonathanlewis.wordpress.com/2015/02/19/255-columns/
--提到这样的情况:
If you have more than 255 columns in a row Oracle will split it into multiple row pieces of 255 columns each plus
one row piece for "the rest"; but the split counts from the end, so if you have a table with 256 columns the first
row-piece has one column the second row-piece has 255 columns. This is bad news for all sorts of operations because
Oracle will have to expend extra CPU chasing the the row pieces to make use of any column not in the first row
piece. The optimists among you might have expected "the rest" to be in the last row piece. If you want to be
reminded how bad row-chaining can get for wide tables, just have a look at an earlier blog note of mine (starting at
this comment).
--实际上以前也提到这个http://jonathanlewis.wordpress.com/2012/03/30/quiz-night-17/
--我自己以前也做了重复测试:http://blog.itpub.net/267265/viewspace-747213/
--但是上面提到如果你的表有256列,行片的保存是第1个行片仅仅包含1个字段,第2个行片包含剩下的255个字段.oracle这样设计不是很不
--科学吗? 自己还是测试看看.
1.建立测试环境:
SCOTT@test01p> @ver1
PORT_STRING VERSION BANNER CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0 12.1.0.1.0 Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
--建表方法:
spool a.sql
select 'create table t1 (' from dual
union all
select 'col'||lpad(rownum-1,3,'0')||' number(1),' from dual connect by levelunion all
select 'constraint t1_pk primary key (col000));' from dual ;
spool off
--检查a.sql,删除一些不需要部分.
SCOTT@test01p> @a
Table created.
2.插入数据:
SCOTT@test01p> insert into t1 (col000,col001) values (1,1);
1 row created.
SCOTT@test01p> commit ;
Commit complete.
SCOTT@test01p> select rowid,t1.col000 from t1;
ROWID COL000
------------------ ----------
AAAXzjAAJAAAAFNAAA 1
SCOTT@test01p> @lookup_rowid AAAXzjAAJAAAAFNAAA
OBJECT FILE BLOCK ROW DBA TEXT
---------- ---------- ---------- ---------- -------------------- ----------------------------------------
97507 9 333 0 9,333 alter system dump datafile 9 block 333 ;
SCOTT@test01p> alter system checkpoint ;
System altered.
SCOTT@test01p> alter system dump datafile 9 block 333 ;
System altered.
3.查看转储文件:
Block header dump: 0x0240014d
Object id on Block? Y
seg/obj: 0x17ce3 csc: 0x00.a4e0a9 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x2400148 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000e.00a.000004c0 0x014005cc.019f.01 --U- 1 fsc 0x0000.00a4e0aa
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x0240014d
data_block_dump,data header at 0xaf08264
===============
tsiz: 0x1f98
hsiz: 0x14
pbl: 0x0af08264
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1f8f
avsp=0x1f7b
tosp=0x1f7b
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0x1f8f
block_row_dump:
tab 0, row 0, @0x1f8f
tl: 9 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 02
col 1: [ 2] c1 02
end_of_block_dump
End dump data blocks tsn: 3 file#: 9 minblk 333 maxblk 333
--这样的情况并没有出现作者说的情况.
4.换1种插入方式:
SCOTT@test01p> drop table t1 purge ;
Table dropped.
SCOTT@test01p> @a
Table created.
SCOTT@test01p> insert into t1 (col000,col255) values (1,1);
1 row created.
SCOTT@test01p> commit ;
Commit complete.
SCOTT@test01p> select rowid,t1.col000 from t1;
ROWID COL000
------------------ ----------
AAAXzlAAJAAAAFNAAB 1
SCOTT@test01p> @lookup_rowid AAAXzlAAJAAAAFNAAB
OBJECT FILE BLOCK ROW DBA TEXT
---------- ---------- ---------- ---------- -------------------- ----------------------------------------
97509 9 333 1 9,333 alter system dump datafile 9 block 333 ;
--注意后面是AAB,也就是行号是1.
SCOTT@test01p> alter system checkpoint ;
System altered.
SCOTT@test01p> alter system dump datafile 9 block 333 ;
System altered.
5.查看转储文件:
Block header dump: 0x0240014d
Object id on Block? Y
seg/obj: 0x17ce5 csc: 0x00.a4e179 itc: 3 flg: E typ: 1 - DATA
brn: 0 bdba: 0x2400148 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000d.01b.000004ea 0x01401721.0121.25 --U- 2 fsc 0x0000.00a4e17b
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x03 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000
bdba: 0x0240014d
data_block_dump,data header at 0xaf07c7c
===============
tsiz: 0x1f80
hsiz: 0x16
pbl: 0x0af07c7c
76543210
flag=--------
ntab=1
nrow=2
frre=-1
fsbo=0x16
fseo=0x1e70
avsp=0x1e5a
tosp=0x1e5a
0xe:pti[0] nrow=2 offs=0
0x12:pri[0] offs=0x1e7c
0x14:pri[1] offs=0x1e70
block_row_dump:
tab 0, row 0, @0x1e7c
tl: 260 fb: -----L-- lb: 0x1 cc: 255
col 0: *NULL*
.... [snip]
col 253: *NULL*
col 254: [ 2] c1 02
tab 0, row 1, @0x1e70
tl: 12 fb: --H-F--- lb: 0x1 cc: 1
nrid: 0x0240014d.0
col 0: [ 2] c1 02
end_of_block_dump
End dump data blocks tsn: 3 file#: 9 minblk 333 maxblk 333
--确实这样,注意看fb的表示:fb: --H-F--- H表示Head of row piece,F表示First data piece.
-- fb: -----L-- L表示Last data piece.
--而且还有1个奇怪的情况:ITL槽的使用出现了3个.
6.做1个猜测,oracle是否从后面一个非NULL的字段开始选择行片.继续重复测试:
drop table t1 purge ;
@a
insert into t1 (col000,col254) values (1,1);
commit ;
alter system checkpoint ;
alter system dump datafile 9 block 333 ;
7.查看转储文件.
Block header dump: 0x0240014d
Object id on Block? Y
seg/obj: 0x17ce7 csc: 0x00.a4e3e1 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x2400148 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0006.00a.000015ae 0x014004f9.03da.2b --U- 1 fsc 0x0000.00a4e3e2
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x0240014d
data_block_dump,data header at 0xaf07c64
===============
tsiz: 0x1f98
hsiz: 0x14
pbl: 0x0af07c64
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1e92
avsp=0x1e7e
tosp=0x1e7e
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0x1e92
block_row_dump:
tab 0, row 0, @0x1e92
tl: 262 fb: --H-FL-- lb: 0x1 cc: 255
col 0: [ 2] c1 02
col 1: *NULL*
... [snip]
col 253: *NULL*
col 254: [ 2] c1 02
end_of_block_dump
End dump data blocks tsn: 3 file#: 9 minblk 333 maxblk 333
--确实这样.一般oracle不保存后面为NULL的字段,所以讲尽量吧NULL的字段放后面.
8.如果这个时候,感觉应该256字段单独1个row piece.继续测试:
update t1 set col255=1 where col000=1;
commit ;
alter system checkpoint ;
alter system dump datafile 9 block 333 ;
Block header dump: 0x0240014d
Object id on Block? Y
seg/obj: 0x17ce7 csc: 0x00.a4e4fd itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x2400148 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0006.00a.000015ae 0x014004f9.03da.2b C--- 0 scn 0x0000.00a4e3e2
0x02 0x0003.000.000018b9 0x0140093d.0341.0e --U- 1 fsc 0x00fa.00a4e4ff
bdba: 0x0240014d
data_block_dump,data header at 0xaf07c64
===============
tsiz: 0x1f98
hsiz: 0x14
pbl: 0x0af07c64
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1e92
avsp=0x1e7e
tosp=0x1f78
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0x1e92
block_row_dump:
tab 0, row 0, @0x1e92
tl: 12 fb: --H-F--- lb: 0x2 cc: 1
nrid: 0x0240014e.0
col 0: [ 2] c1 02
end_of_block_dump
End dump data blocks tsn: 3 file#: 9 minblk 333 maxblk 333
--昏倒!修改最后1个字段从null到1,导致分成2个row pieces,并且col000字段作为1个row piece,而剩下的255个字段作为第2个行片.
--nrid: 0x0240014e.0 这个指示第2行片的dba,而且不在当前块中,出现了行迁移. 搞不懂为什么剩下的数据信息不保存在当前块中.
SCOTT@test01p> @dfb 0240014e
RFILE# BLOCK#
---------- ----------
9 334
TEXT
----------------------------------------
alter system dump datafile 9 block 334 ;
SCOTT@test01p> alter system checkpoint ;
System altered.
SCOTT@test01p> alter system dump datafile 9 block 334 ;
System altered.
9.再次查看转储文件内容:
Block header dump: 0x0240014e
Object id on Block? Y
seg/obj: 0x17ce7 csc: 0x00.a4e3e1 itc: 3 flg: E typ: 1 - DATA
brn: 0 bdba: 0x2400148 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0003.000.000018b9 0x0140093d.0341.0d --U- 1 fsc 0x0000.00a4e4ff
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x03 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000
bdba: 0x0240014e
data_block_dump,data header at 0xaf07c7c
===============
tsiz: 0x1f80
hsiz: 0x14
pbl: 0x0af07c7c
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1e7a
avsp=0x1e66
tosp=0x1e66
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0x1e7a
block_row_dump:
tab 0, row 0, @0x1e7a
tl: 262 fb: -----L-- lb: 0x1 cc: 255
col 0: *NULL*
.... [snip]
col 252: *NULL*
col 253: [ 2] c1 02
col 254: [ 2] c1 02
end_of_block_dump
End dump data blocks tsn: 3 file#: 9 minblk 334 maxblk 334
--注意itl=3,tl =262 ,file=9,block333应该放的下,只所以选择放在别的数据块,估计oracle根本这时根本考虑是否放下,而且直接重组,
--也许大部分情况一个行片的长度会很大.选择放到别的块是正确的.
9.还记得以前做过的测试，链接：[20121025]1条记录会有多少row pieces.txt
http://blog.itpub.net/267265/viewspace-747213/
spool a.sql
select 'create table t1 (' from dual
union all
select 'col'||lpad(rownum-1,3,'0')||' number(1),' from dual connect by levelunion all
select 'constraint t1_pk primary key (col000));' from dual ;
SQL> insert into t1(col000) values(1);
SQL> commit;
begin
for i in 255..999 loop
execute immediate 'update t1 set col'||i||'=0';
end loop;
commit;
end;
/
--这样操作，可以作出许多row piece。第1次修改col255(256列),分片是col000作为1piece，字段col001-col255作为1piece。
--继续修改修改col256(257列),分片是col000作为1piece,字段col002作为1piece,字段col2-col256作为1piece。如此下去产生许多行片。
--注意看row piece的分割，前面的row piece并不合并。
总结:
--建立表字段太多不好,如果太多不要超过255,可能的话可以根据业务需要拆分多个表.实际上我个人在实际系统很少遇到,至少对于我管的系
统,如果出现,开发者应该想想是否建模存在问题.据说出现在像Siebel应用很常见(我从来没有见过)
