Oracle decompress

Oracle "Compress" feature was introduced in early 11G. Anyone aware of any customers actually using that feature at the fields and if we know it has any adverse effect in general? It seems like a pretty promising feature for cutting down space usage and at the same time having some performance gains. I am tempted to have couple of my customers to try it out on vCenter database where vpx_event and vpx_event_args are growing out of control. If we can make use of this feature without major drawbacks, it can be helpful in other areas of our product as well where we have large analytic data reside. VPX_HIST_TEMP* are one of those examples as well.

Having to set this to compress all, all the data onward will be compressed and existing data remain uncompressed.

Note: "alter table abc compress for all operations" and "alter table abc compress for oltp" basically the same thing with different name.

SQL> alter table RESOURCEFULNESS compress for all operations;

Table altered.

SQL> select  compression, compress_for, table_name from user_tables where table_name like 'RESOURCEUDFKIND';

COMPRESS COMPRESS_FOR TABLE_NAME

-------- ------------ ------------------------------

DISABLED                              RESOURCEUDF

ENABLED  OLTP                    RESOURCEUDFKIND

Another example to illustrate on how to compress and to reset to nocompress.

SQL> alter table vpx_event compress for oltp;

Table altered.

SQL> select  table_name, compression, compress_for from dba_tables where compression ='ENABLED' OR COMPRESS_FOR='DISABLED';

TABLE_NAME                     COMPRESS COMPRESS_FOR

------------------------------ -------- ------------

VPX_EVENT                      ENABLED  OLTP

MGMT_METRICS                   ENABLED  OLTP

MGMT_POLICIES                  ENABLED  OLTP

SQL> alter table vpx_event nocompress;

Table altered.

SQL> select  table_name, compression, compress_for from dba_tables where compression ='ENABLED' OR COMPRESS_FOR='DISABLED';

TABLE_NAME                     COMPRESS COMPRESS_FOR

------------------------------ -------- ------------

MGMT_METRICS                   ENABLED  OLTP

MGMT_POLICIES                  ENABLED  OLTP

SQL>

Second example from another 12C database

SQL> col table_name format a30
SQL> col compress format a15
SQL> select  table_name, compression, compress_for from dba_tables where compression ='ENABLED' OR COMPRESS_FOR='DISABLED';

no rows selected

SQL> alter table vpx_event compress for oltp;

Table altered.

SQL> select  table_name, compression, compress_for from dba_tables where compression ='ENABLED' OR COMPRESS_FOR='DISABLED';

TABLE_NAME                     COMPRESS COMPRESS_FOR
------------------------------ -------- ------------------------------
VPX_EVENT                      ENABLED  ADVANCED

SQL> alter table vpx_event nocompress;

Table altered.

SQL> select  table_name, compression, compress_for from dba_tables where compression ='ENABLED' OR COMPRESS_FOR='DISABLED';

no rows selected


SQL>