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>
