Tom在《Expert Oracle Database Architecture 9i and 10g Programming Techniques and Solutions》一书的第10章中说:
Oracle will internally store a row with more than 254 columns in separate row pieces that point to each other and must be reassembled to produce the entire row image.
而在Oracle concepts官方文档中:
When a table has more than 255 columns, rows that have data after the 255th column are likely to be chained within the same block. This is called intra-block chaining. A chained row’s pieces are chained together using the rowids of the pieces. With intra-block chaining, users receive all the data in the same block. If the row fits in the block, users do not see an effect in I/O performance, because no extra I/O operation is required to retrieve the rest of the row.
经过实际测试,Oracle是按照255个列组成一个row pieces的,而不是254。
NING@ning>select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
NING@ning>exec test_cols(254);
PL/SQL procedure successfully completed.
NING@ning>exec test_cols(255);
PL/SQL procedure successfully completed.
NING@ning>exec test_cols(256);
PL/SQL procedure successfully completed.
NING@ning>insert into cols254(a1,a254) values(1,1);
1 row created.
NING@ning>insert into cols255(a1,a254,a255) values(1,1,1);
1 row created.
NING@ning>insert into cols256(a1,a254,a255,a256) values(1,1,1,1);
1 row created.
NING@ning>select dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) from cols254;
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------ ------------------------------------
4 167
NING@ning>select dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) from cols255;
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------ ------------------------------------
4 175
NING@ning>select dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) from cols256;
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------ ------------------------------------
4 183
NING@ning>alter system dump datafile 4 block 167;
System altered.
NING@ning>alter system dump datafile 4 block 175;
System altered.
NING@ning>alter system dump datafile 4 block 183;
System altered.
分析得到的trace文件,可以看到,cols254,cols255的列都只有一个row piece,而cols256则有2个row piece,第一个piece包含的列数cc为1,第二个piece包含的列数cc为255。
通过测试还可以知道,oracle划分row piece的时候,是从最后的列往前数,每255个列为一个row piece。也就是除了第一个row piece的列数可能小于255,其余的row piece都是包含255个列的。
当然,由于Oracle不保存尾NULL值,如果一个table有超过255个列定义,但是表中的数据,255列以后的值都是NULL的话,则Oracle只保存一个row piece。
关于test_cols过程,请参考:http://ningoo.itpub.net/post/2149/265635
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/193161/viewspace-50289/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/193161/viewspace-50289/