ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Tom新书中的一个小问题

Tom新书中的一个小问题

原创 Linux操作系统 作者:NinGoo 时间:2019-06-08 15:39:07 0 删除 编辑

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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2004-12-07

  • 博文量
    200
  • 访问量
    133196