SQL> create table CH1(c char(1)) pctfree 0 compress;
表已创建。
SQL> create table CH2(c char(1)) pctfree 0 compress;
表已创建。
SQL> create table CH3(c char(1)) pctfree 0 NOcompress;
表已创建。
SQL> insert /*+ APPEND */ into CH1 select 'c' from dual connect by level<3000;
已创建 2999 行。
SQL> insert /*+ NO_APPEND */ into CH2 select 'c' from dual connect by level<3000;
已创建 2999 行。
SQL> insert /*+ APPEND */ into CH3 select 'c' from dual connect by level<3000;
已创建 2999 行。
SQL> commit;
提交完成。
SQL> select dbms_rowid.rowid_block_number(rowid) bn,count(*) from CH1 group by dbms_rowid.rowid_block_number(rowid);
BN COUNT(*)
---------- ----------
9685 730
9684 730
9687 79
9683 730
9686 730
SQL> select dbms_rowid.rowid_block_number(rowid) bn,count(*) from CH2 group by dbms_rowid.rowid_block_number(rowid);
BN COUNT(*)
---------- ----------
9691 67
9695 733
9693 733
9694 733
9692 733
SQL> select dbms_rowid.rowid_block_number(rowid) bn,count(*) from CH3 group by dbms_rowid.rowid_block_number(rowid);
BN COUNT(*)
---------- ----------
140630 730
140629 730
140627 730
140628 730
140631 79
It probably matters about APPEND, rather than COMPRESS.
I was curious about this too, and tried to dump the blocks out to trace, then compared the differences between the trace files. And the ITL just caught my eyes on it.
SQL> show user
USER 为 "Z"
SQL> select segment_name,file_id from dba_extents where owner='Z' and segment_name in ('CH1','CH2');
SEGMENT_NA FILE_ID
---------- ----------
CH1 6
CH2 6
SQL> alter system dump datafile 6 block 9686;
系统已更改。
SQL> exit
....
SQL> alter system dump datafile 6 block 9692;
系统已更改。
SQL> exit
The APPEND consumes 3 ITL slots (which means it takes 3*24 bytes=72 bytes), as shown bellow:
Block header dump: 0x018025d5
Object id on Block? Y
seg/obj: 0x1adc6 csc: 0x01.ee3de15 itc: 3 flg: E typ: 1 - DATA
brn: 0 bdba: 0x18025d0 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0005.00e.000010fc 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000bdba: 0x018025d5
data_block_dump,data header at 0xbb52e7c
===============
tsiz: 0x1f80
hsiz: 0x5c6
pbl: 0x0bb52e7c
76543210
flag=--------
ntab=1
nrow
=730While the NOAPPEND consumes 2 ITL slots (which means it takes 2*24 bytes=48 bytes)
Block header dump: 0x018025dc
Object id on Block? Y
seg/obj: 0x1adc7 csc: 0x01.ee3de4b itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x18025d8 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a.017.00000e6e 0x01400443.03fc.1c --U- 733 fsc 0x0000.0ee3de4d
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000bdba: 0x018025dc
data_block_dump,data header at 0xbb55064
===============
tsiz: 0x1f98
hsiz: 0x5cc
pbl: 0x0bb55064
76543210
flag=--------
ntab=1
nrow=
733"The minimum row size for a non-clustered table is 9 bytes. Therefore, if the calculated value for an average row size is less than this absolute minimum row size, use the minimum value as the average row size".
I am not quite good at this, but plz allow me to guess this boldly: the ITL(
24 bytes) could be the reason(at least one of the reasons) why the APPEND stores 3 rows(
3*9bytes ~3*11 bytes) less than the NOAPPEND one.