Compression -- 5 : OLTP Compression

Taking the test case from my first Blog Post on compression,  is there any way to support OLTP ?
As demonstrated in the first Blog Post and here, once the table undergoes DML, its size changes.

PDB1@ORCL> select segment_name, segment_type, bytes/1048576
  2  from user_segments
  3  where segment_name in ('SOURCE_DATA','COMPRESSED_1')
  4  /

SEGMENT_NAME         SEGMENT_TYPE   BYTES/1048576
------------------------------ ------------------ -------------
COMPRESSED_1         TABLE        12
SOURCE_DATA         TABLE        49

PDB1@ORCL> 
PDB1@ORCL> update compressed_1
  2  set owner=owner;

364496 rows updated.

PDB1@ORCL> commit;

Commit complete.

PDB1@ORCL> select bytes/1048576
  2  from user_segments
  3  where segment_name = 'COMPRESSED_1';

BYTES/1048576
-------------
    22

PDB1@ORCL> 
PDB1@ORCL> update compressed_1
  2  set owner=substr(owner||owner,1,30);

364496 rows updated.

PDB1@ORCL> commit;

Commit complete.

PDB1@ORCL> select bytes/1048576
  2  from user_segments
  3  where segment_name = 'COMPRESSED_1';

BYTES/1048576
-------------
    58

PDB1@ORCL> 

So, I setup another table that supports OLTP compression.  This requires the Advanced Compression licence.  Make sure that you have purchased the licence !

PDB1@ORCL> create table compressed_2 tablespace hemant COMPRESS FOR OLTP
  2  as select * from source_data where 1=2;

Table created.

PDB1@ORCL> insert /*+ APPEND */ into compressed_2
  2  select * from source_data;

364496 rows created.

PDB1@ORCL> commit;

Commit complete.

PDB1@ORCL> select bytes/1048576
  2  from user_segments 
  3  where segment_name = 'COMPRESSED_2';

BYTES/1048576
-------------
    13

PDB1@ORCL> update compressed_2
  2  set owner=owner;

364496 rows updated.

PDB1@ORCL> commit;

Commit complete.

PDB1@ORCL> select bytes/1048576
  2  from user_segments
  3  where segment_name = 'COMPRESSED_2';

BYTES/1048576
-------------
    13

PDB1@ORCL> update compressed_2
  2  set owner=substr(owner||owner,1,30);

364496 rows updated.

PDB1@ORCL> select bytes/1048576
  2  from user_segments
  3  where segment_name = 'COMPRESSED_2';

BYTES/1048576
-------------
    29

PDB1@ORCL> 
PDB1@ORCL> select table_name, pct_free, compression, compress_for
  2  from user_tables
  3  where table_name like 'COMPRESS%'
  4  order by 1;

TABLE_NAME    PCT_FREE COMPRESS
------------------------------ ---------- --------
COMPRESS_FOR
------------------------------
COMPRESSED_1    0 ENABLED
BASIC

COMPRESSED_2          10 ENABLED
ADVANCED


PDB1@ORCL> 

Note the initial size of COMPRESSED_2 is slightly large because it starts with PCT_FREE=10.
The UPDATE does seem to take longer to run.  COMPRESS FOR OLTP preserves PCT_FREE at 10. But it does handle UPDATEs better than BASIC Compression. However, if you have no subsequent UPDATEs to the data, BASIC Compression (which does not need an additional licence when running Enterprise Edition) will suffice.

I urge you to *TEST* COMPRESS FOR OLTP with the different types of UPDATE operations that you actually have in your live database before you choose to implement it.

In 12c, COMPRESS FOR OLTP is called ROW STORE COMPRESS ADVANCED.  I presented the 11g style COMPRESS FOR OLTP syntax so that you could use it in 11g.
.
.
.