In this blog we’re going to discuss some of the most common, user asked, questions related to Hybrid Columnar Compression (HCC), these questions include:

• How
  to enable Hybrid Columnar Compression for existing tables and partitions?
• Which
  applications (OLTP or Data Warehouse) are best for Hybrid Columnar Compression?
• Does
  Hybrid Columnar Compression require a separate license?

For existing
tables and partitions, there are three best practice approaches to enabling
Hybrid Columnar Compression. Both methods enable Hybrid Columnar Compression
for existing data and will compress future data loaded via bulk loads, the
difference between the methods is whether the table remains accessible while
HCC compression is being enabled.

– Online Redefinition (DBMS_REDEFINITION)

Table remains online for both read/write activity while the statement is executing.

– ALTER TABLE … MOVE PARTITION … ONLINE

Partition/subpartition remains online for both read/write activity while the statement is executing.

– ALTER TABLE … MOVE COLUMN STORE COMPRESS
FOR …

ALTER TABLE MOVE does not permit DML, against the table, while the statement is executing.

Which method should you choose? Well,
it’s really up to you. If you want to keep tables and partitions online and
available while compression is being enabled, then using online redefinition or
ALTER TABLE MOVE PARTITION ONLINE would be best. If you are taking some
downtime to compress, then use ALTER TABLE MOVE – but no matter which is used,
all will provide the same level of compression.

The question regarding which type of
application is best suited for HCC actually isn’t really a common question, but
it should be because it is important to understand the answer to this question.
HCC compression can be used in both OLTP and Data Warehouse applications, but,
and this is important, it is recommended that HCC be enabled only tables or
partitions with no, or infrequent, DML INSERT/UPDATE operations. While data in
Hybrid Columnar compressed tables can be modified using conventional Data
Manipulation Language (DML) operations – INSERT, UPDATE, DELETE – performing
such operations could result in a reduction of the HCC compression ratio – and
if performed frequently, could degrade the HCC compression ratio over time
(requiring the table/partition be re-compressed). If frequent DML operations
are planned on a table or partition, then Advanced Row Compression is better
suited for such data.

Whether or not HCC requires a separate
license is also often misunderstood by users. To use HCC you must be running Oracle
Database Enterprise Edition (11.2.0.3 and above) and, this is also important,
you must be using a HCC supported Oracle platform, including Exadata,
SuperCluster, ZFS Storage Appliance or
FS Flash Storage System.

Oracle Database Appliance (called ODA) does not support HCC.

The database storage optimization
adventure continues in the next blog, in which we will discuss how Advanced Row
Compression and Hybrid Columnar Compression are directly related to Information
Lifecycle Management (ILM) best practices.