Oracle concepts中，表中的一行最大1000 column，每255 column会分成一个row piece，下面使用实验来证明它
创建一个1000 column的行
SQL> begin
2execute immediate 'create table t(col1 char)';
3 for i in 2 .. 1000 loop
4 execute immediate 'alter table t add (col'||i||' char)';
5end loop;
6end;
7/
PL/SQL procedure successfully completed.
结果可以创建。再创建一个1001 column的行
SQL> drop table t;
Table dropped.
SQL> begin
2execute immediate 'create table t(col1 char)';
3 for i in 2 .. 1001 loop
4 execute immediate 'alter table t add (col'||i||' char)';
5end loop;
6end;
7/
begin
*
ERROR at line 1:
ORA-01792: maximum number of columns in a table or view is 1000
ORA-06512: at line 4
结果创建到1001个时报错
接下来创建一个260 column的行的表，再insert数据，dump出来看一行是不是分为两个row piece
SQL> drop table t;
Table dropped.
SQL> begin
2execute immediate 'create table t(col1 number)';
3 for i in 2 .. 260 loop
4 execute immediate 'alter table t add (col'||i||' number)';
5end loop;
6end;
7/
PL/SQL procedure successfully completed.
insert into t values(
1,
2,
3,
4
。
。
(省略)
。
。
253,
254,
255,
256,
257,
258,
259,
260);
SQL> select dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) from t;
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------ ------------------------------------
16
406
SQL> alter system dump datafile 16 block 406;
System altered.
查看DUMP文件
[oracle@myora10 udump]$ more myora10_ora_31787.trc
/d01/app/oracle/admin/myora10/udump/myora10_ora_31787.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /d01/app/oracle/product/10.2.0
System name:Linux
Node name:myora10
Release:2.6.9-67.ELsmp
Version:#1 SMP Fri Nov 16 12:48:03 EST 2007
Machine:i686
Instance name: myora10
Redo thread mounted by this instance: 1
Oracle process number: 17
Unix process pid: 31787, image: oracle@myora10 (TNS V1-V3)
。
。
(省略)
。
。
block_row_dump:
tab 0, row 0, @0x1be1
tl: 927 fb: -----L-- lb: 0x1cc: 255
col0: [ 2]c1 07
col1: [ 2]c1 08
col2: [ 2]c1 09
col3: [ 2]c1 0a
col4: [ 2]c1 0b
col5: [ 2]c1 0c
。
。
(省略)
。
。
col 253: [ 3]c2 03 3c
col 254: [ 3]c2 03 3d
tab 0, row 1, @0x1bc9
tl: 24 fb: --H-F--- lb: 0x1cc: 5
nrid:0x04000196.0
col0: [ 2]c1 02
col1: [ 2]c1 03
col2: [ 2]c1 04
col3: [ 2]c1 05
col4: [ 2]c1 06
end_of_block_dump
End dump data blocks tsn: 16 file#: 16 minblk 406 maxblk 406
从上面的DUMP文件可以看出，一个row分成了两个row piece
tab 0, row 1, @0x1bc9
tl: 24 fb: --H-F--- lb: 0x1cc: 5
nrid:0x04000196.0
看上面的fb，标示为H和F，H代表Head piece of row ，F代表First data piece
nrid:0x04000196.0下一个row piece的地址
tab 0, row 0, @0x1be1
tl: 927 fb: -----L-- lb: 0x1cc: 255
看上面的fb，标示为L，L代表Last data piece
链接(jason_wang2002的个人空间)
http://space.itpub.net/8745319/viewspace-573079
[ 本帖最后由 jason_wang2002 于 2009-3-19 14:41 编辑 ]
|
