BASIC table compression: use with care

articles: 

BASIC table compression is included with Enterprise edition, and can achieve respectable compression ratios. But beware! Subsequent DML may be disastrous.

Actually, BASIC compression (or ROW STORE COMPRESS BASIC to use the current syntax) isn't compression at all: it is de-duplication within blocks. If the same string occurs more than once in a block, it is stored only once with tokens pointing to it where needed. None-the-less, if your data is amenable to it, the ratios can be respectble. I get better than 4:1 here:

orclz>
orclz> create table t1 as select * from all_objects;

Table created.

orclz> select blocks from user_segments where segment_name='T1';

    BLOCKS
----------
      1664

orclz> drop table t1;

Table dropped.

orclz> create table t1 compress as select * from all_objects;

Table created.

orclz> select blocks from user_segments where segment_name='T1';

    BLOCKS
----------
       384

orclz>

But now I'll do some DML, that doesn't even change anything:
orclz> update t1 set owner=owner;

90631 rows updated.

orclz> select blocks from user_segments where segment_name='T1';

    BLOCKS
----------
      1792

orclz>

Holy smoke! And that is without even considering performance (next blog article...) This gives you an idea of what has gone wrong:
orclz>
orclz> @?\rdbms\admin\utlchain

Table created.

orclz> analyze table t1 list chained rows;

Table analyzed.

orclz> select count(*) from chained_rows;

  COUNT(*)
----------
     83755

orclz>

What do you think of that? Nearly every row migrated. I'll take this further in another article soon.
--
John watson
Oracle Certified Master DBA
http://skillbuilders.com

Comments

Yeah, well you cleaned up that little exclamation, didn't you?

Another awesome article, John. Thank you.

Now I might be making this up, but I had the impression - either from reading of from imagining - that Basic Compression didn't survive conventional path DML. i.e. Updates, Deletes and non-Direct-Path Inserts would not preserve compression.

Now if this is the case, then when a skinny compressed row gets updated, it also gets fat and decompressed. Since they are packed in so tight, they bust their PCTFREE reserve of free space in the block straight away and end up migrating to another block. So, if I'm not making this up, then this result is entirely understandable.

But I wonder what would happen if we ENABLE ROW MOVEMENT? I guess nothing, but wouldn't it be cool if Oracle saw this little time bomb coming and elected to create a work around? To explain my reasoning (although I'm sure John gets it), unless a table has ENABLE ROW MOVEMENT specified, then a row's ROWID address is guaranteed not to change, and row-chaining is the solution to preserving ROWID of a row that can no longer fit in its block. If we ENABLE ROW MOVEMENT then there is no reason to preserve the ROWID except to avoid impact on indexes, so it would be okay to fully migrate the row - including the ROWID - rather than chaining.

So why don't I test it myself? Two reasons: no access to an Oracle database at the moment, and I'm one of those annoying people who enjoy the journey more than the destination.

A change of behaviour if row movement were enabled is a brilliant idea - but it will require an enhancement request to Uncle Oracle. Shame!