Tom新书中的一个小问题

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/