Space Requirement for Compressed Row Updates
It seems that when you update rows within compressed blocks the data is temporarily decompressed then recompressed, possibly causing the table to grow.
Here I create an uncompressed copy of DBA_OBJECTS, which takes 788 blocks to store. The compressed copy is then shown to take 256 blocks.
On updating all of the rows the table has grown to 1038 blocks, approximately 256+788, yet as the DBMS_ROWID-based queries show the rows have not moved. It seems that an additional approximately-788 blocks of space have been added to move the table's high water mark.
Curioser and curioser.
SQL> drop table t1
2
Table dropped.
SQL>
SQL> create table t1
2 nocompress
3 pctfree 0
4 as
5 select * from dba_objects
6 /
Table created.
SQL>
SQL> exec dbms_stats.gather_table_stats(user,'T1')
PL/SQL procedure successfully completed.
SQL>
SQL> select blocks,empty_blocks from user_tables
2 where table_name ='T1'
3 /
BLOCKS EMPTY_BLOCKS
---------- ------------
788 0
SQL>
SQL> select min(dbms_rowid.rowid_block_number(rowid)) min_block,
2 max(dbms_rowid.rowid_block_number(rowid)) max_block,
3 count(distinct dbms_rowid.rowid_block_number(rowid)) num_blocks
4 from t1
5 /
MIN_BLOCK MAX_BLOCK NUM_BLOCKS
---------- ---------- ----------
1610346 1611133 788
SQL>
SQL> update t1 set owner = rtrim(owner)
2 /
60120 rows updated.
SQL> commit
2 /
Commit complete.
SQL>
SQL> exec dbms_stats.gather_table_stats(user,'T1')
PL/SQL procedure successfully completed.
SQL>
SQL> select blocks,empty_blocks from user_tables
2 where table_name ='T1'
3 /
BLOCKS EMPTY_BLOCKS
---------- ------------
788 0
SQL> select min(dbms_rowid.rowid_block_number(rowid)) min_block,
2 max(dbms_rowid.rowid_block_number(rowid)) max_block,
3 count(distinct dbms_rowid.rowid_block_number(rowid)) num_blocks
4 from t1
5 /
MIN_BLOCK MAX_BLOCK NUM_BLOCKS
---------- ---------- ----------
1610346 1611133 788
SQL>
SQL> drop table t1
2 /
Table dropped.
SQL>
SQL> create table t1
2 compress
3 pctfree 0
4 as
5 select * from dba_objects
6 /
Table created.
SQL>
SQL> exec dbms_stats.gather_table_stats(user,'T1')
PL/SQL procedure successfully completed.
SQL>
SQL> select blocks,empty_blocks from user_tables
2 where table_name ='T1'
3 /
BLOCKS EMPTY_BLOCKS
---------- ------------
256 0
SQL> select min(dbms_rowid.rowid_block_number(rowid)) min_block,
2 max(dbms_rowid.rowid_block_number(rowid)) max_block,
3 count(distinct dbms_rowid.rowid_block_number(rowid)) num_blocks
4 from t1
5 /
MIN_BLOCK MAX_BLOCK NUM_BLOCKS
---------- ---------- ----------
1611146 1611401 256
SQL>
SQL> update t1 set owner = rtrim(owner)
2 /
60121 rows updated.
SQL> commit
2 /
Commit complete.
SQL>
SQL> exec dbms_stats.gather_table_stats(user,'T1')
PL/SQL procedure successfully completed.
SQL>
SQL> select blocks,empty_blocks from user_tables
2 where table_name ='T1'
3 /
BLOCKS EMPTY_BLOCKS
---------- ------------
1038 0
SQL> select min(dbms_rowid.rowid_block_number(rowid)) min_block,
2 max(dbms_rowid.rowid_block_number(rowid)) max_block,
3 count(distinct dbms_rowid.rowid_block_number(rowid)) num_blocks
4 from t1
5 /
MIN_BLOCK MAX_BLOCK NUM_BLOCKS
---------- ---------- ----------
1611146 1611401 256
SQL> update t1 set owner = lower(owner)
2 /
60121 rows updated.
SQL> commit
2 /
Commit complete.
SQL> exec dbms_stats.gather_table_stats(user,'T1')
PL/SQL procedure successfully completed.
SQL>
SQL> select blocks,empty_blocks from user_tables
2 where table_name ='T1'
3 /
BLOCKS EMPTY_BLOCKS
---------- ------------
1038 0
SQL> select min(dbms_rowid.rowid_block_number(rowid)) min_block,
2 max(dbms_rowid.rowid_block_number(rowid)) max_block,
3 count(distinct dbms_rowid.rowid_block_number(rowid)) num_blocks
4 from t1
5 /
MIN_BLOCK MAX_BLOCK NUM_BLOCKS
---------- ---------- ----------
1611146 1611401 256
posted by David Aldridge @ 12:21 PM
4 Comments:
they migrated - rowids won't change when they migrate - but the rows are "chained"
ops$tkyte@ORA10GR2> create table CHAINED_ROWS (
2 owner_name varchar2(30),
3 table_name varchar2(30),
4 cluster_name varchar2(30),
5 partition_name varchar2(30),
6 subpartition_name varchar2(30),
7 head_rowid rowid,
8 analyze_timestamp date
9 );
Table created.
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> create table t1
2 compress
3 as
4 select * from dba_objects
5 /
Table created.
ops$tkyte@ORA10GR2> analyze table t1 list chained rows;
Table analyzed.
ops$tkyte@ORA10GR2> select count(*) from chained_rows;
COUNT(*)
----------
0
ops$tkyte@ORA10GR2> update t1 set owner = rtrim(owner);
50819 rows updated.
ops$tkyte@ORA10GR2> commit;
Commit complete.
ops$tkyte@ORA10GR2> analyze table t1 list chained rows;
Table analyzed.
ops$tkyte@ORA10GR2> select count(*) from chained_rows;
COUNT(*)
----------
38989
the rowid points to the head rowpiece - which could just be a rowid to where the row currently really is.
Tsh, migration of course. So they do decompress after all.
I always thought that the updates went back in uncompressed and probably wouldn't fit... I might be right then
and hey, what do you mean 7 days is two months - no wonder you quit being an engineer ;)
Yes, that's what i thought -- then I confabulated myself by forgetting about migrated row rowid's. I didn't know whether I was coming or going in the end.
And it's all to do with timezones ... international date lines etc.. really Pete I do wish you'd pay attention.
Post a Comment
<< Home