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
Advertisements

4 thoughts on “Space Requirement for Compressed Row Updates

  1. 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.

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

  3. 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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s