Hybrid Columnar Compression Common Questions

By Gregg Christman-Oracle on Feb 26, 2016

Similar to last week, 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. See here for more information regarding the restrictions that apply to the online redefinition of tables.

- 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.

Much more information regarding Hybrid Columnar Compression best practices is available. Please see this Hybrid Columnar Compression white paper (pages 6 to 8) for more information.

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.

출처 : https://blogs.oracle.com/DBStorage/entry/hybrid_columnar_compression_common_questions