.comment-link {margin-left:.6em;}

Oracle Sponge -- Now Moved To Wordpress

Please use http://oraclesponge.wordpress.com

Tuesday, April 18, 2006

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

4 Comments:

At 1:02 PM, Blogger Thomas Kyte said...

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.

 
At 1:27 PM, Blogger David Aldridge said...

Tsh, migration of course. So they do decompress after all.

 
At 2:48 PM, Blogger Pete_S said...

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 ;)

 
At 3:22 PM, Blogger David Aldridge said...

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

Links to this post:

Create a Link

<< Home