ADVANCED table compression: an improvement

articles: 

Following previous blog re BASIC compression, here are a couple of simple tests with Advanced Compression - which is supposed to survive conventional DML.

For update, it is certainly more economical in space than basic compression, but it is by no means perfect and the row migration issue is still the same:

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

Table created.

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

    BLOCKS
----------
       512

orclz> update t1 set owner=owner;

90631 rows updated.

orclz> @?\rdbms\admin\utlchain

Table created.

orclz> analyze table t1 list chained rows;

Table analyzed.

orclz> select count(*) from chained_rows;

  COUNT(*)
----------
     80713

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

    BLOCKS
----------
      1024

orclz>

The real advantage of Advanced Compression is that it works with conventional INSERTs that go through the cache:
orclz>
orclz> create table bas_comp row store compress basic as select * from all_objects where 1=2;

Table created.

orclz> create table adv_comp row store compress advanced as select * from all_objects where 1=2;

Table created.

orclz> insert all into bas_comp into adv_comp select * from all_objects;

181268 rows created.

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

    BLOCKS
----------
      1408

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

    BLOCKS
----------
       512

orclz>
What about performance? Next article....

--
John Watson
Oracle Certified Master DBA
http://skillbuilders.com