Hybrid Columnar Compression ( HCC ) in Oracle Exadata -1

I will explain Hybrid Columnar compression ( HCC ) feature in Oracle Exadata in this article.

Hybrid Columnar Compression ( HCC ) in Oracle Exadata

Already with the Basic and Advance Compression features, Oracle has introduced a new Compression technique ( Hybrid Columnar Compression ) with the Exadata machine. The HCC provides very high levels of data compression and a significant storage gain for I/O performance.

HCC is available not  only Exadata but also the ZFS, which is Oracle’s Storage Appliance.  ZFS Storage Appliance is generally used for backup and clone environment and it is very successful for this task.

Hybrid Columnar Compression ( HCC )

According to the preferred option for HCC, Compression can be done up to 50 times.  HCC stores the same column for a group of rows together. HCC uses column and row based methods to store data in logical compression units. Compression unit is a unit that stores more than one block in each block as shown below.

HCC in Exadata 

HCC can compress Tables,partitions and tablespaces with Warehouse and Archive mode. Each method offers two different methods, high and low in itself, and this method determines how much storage it gains and how well performance is.

HCC Types in Exadata

HCC is not available  for index and lob segments. It is not recommended to apply HCC to tables that update very often. Because when the compressed data is updated, the entire compression unit will be locked and the data will be moved to a non-compression or OLTP location. We have two different compression methods, Warehouse and Archive.

• Warehouse Compression ( Compress for Query Low | High ): This method is suitable for Datawarehouse environments and designed optimal performance for large queries. COMPRESS FOR QUERY HIGH  default compression method for warehouse environments.
• Archive Compression ( Compress for Archive low | high ): It is a method that makes a high amount of compression and very good storage gain for data that is not frequently updated. COMPRESS FOR ARCHIVE LOW is the default method for Archive compression.

Before compression process , we need to decide for Maximum Storage gain or Maximum performance.  In other words, If you want to perform maximum compression and storage saving, then you need to choose Archive compression. But If you want to perform maximum performance during compression, then you need to choose Warehouse compression.

When you choose Archive method, the compression is very good but the performance is low. In the Archive method, Archive Low is more efficient than Archive High, and those who want a bit of performance can try it. You can use both Query High and Query Low methods for both compression and performance.

The following command is used to compress an existing table with the Warehouse query low method.

SQL> ALTER TABLE MEHMET.DEVECI MOVE COMPRESS FOR QUERY LOW;

The following command is used to compress an existing table with the Warehouse query high method.

SQL> ALTER TABLE MEHMET.DEVECI MOVE COMPRESS FOR QUERY HIGH;

The following command is used to compress an existing table with the Archive low method.

SQL> ALTER TABLE MEHMET.DEVECI COMPRESS FOR ARCHIVE LOW;

The following command is used to compress an existing table with Archive High.

SQL> ALTER TABLE MEHMET.DEVECI COMPRESS FOR ARCHIVE LOW;

The following command is used to compress an existing partition.

SQL> ALTER TABLE MEHMET.DEVECI MODIFY PARTITION FATURA_01022018 COMPRESS FOR ARCHIVE LOW;

Compressed table can be canceled with the following command.

SQL> ALTER TABLE MEHMET.DEVECI NOCOMPRESS;

SQL> ALTER TABLE MEHMET.DEVECI MOVE NOCOMPRESS;

We can query which table or partition is compressed by the following commands.

SQL> SELECT owner,table_name,compress_for FROM dba_tables WHERE compression= 'ENABLED';

SQL> SELECT owner,table_name,partition_name,compress_for FROM dba_tab_partitions WHERE compression= 'ENABLED';

SQL> SELECT rowid,owner,object_name FROM sales;

SQL> SELECT dbms_compression.get_compression_type('USER', 'TABLENAME', 'ROW_ID') FROM dual;

Read the second article of Hybrid Columnar Compression

Hybrid Columnar Compression ( HCC ) in Oracle Exadata -2

Do you want to learn Exadata detailed, then read the following articles.