Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
Hybrid Columnar Compression (HCC) introduced with Oracle Database 11g Release 2. HCC is a feature that can be used with Exadata storage, Pillar Axiom storage systems and Sun ZFS storage appliance.
Using HCC, it is possible to compress tables from 10 to 50 times. In tables where HCC is used, data is kept in the column store structure for better compression and deduplication. The following figure shows the difference between Row store and Column store.
Keeping data in the form of a column store normally has a negative impact on performance. However, with HCC feature, tables are divided into pieces and this disadvantage has been transformed into a very serious advantage and provides very significant gains in the I/O operations. These parts are called compression units. Each compression unit contains a subset of rows and is arranged in the column order. Compression units are larger than database blocks. The Compression unit size ranges from 32K to 256K. Generally it is around 32K.
Hybrid Columnar Compression Types:
There are two types of HCC.
Warehouse Compression: It aims to improve performance while compressing data. Optimized for speed. It can compress data up to 10 times. It provides up to 10 times reduction in “I/O” for scan operations. Optimized for Smart Scan.
Archive Compression: Optimized for more compression. It can compress data from 15 to 50 times. Archive compression is an ideal compression type for tables with old data that is not frequently accessed.
We can use LOW and HIGH to determine the compression level in both types.
HCC uses LZO, ZLIB, and BZ2 compression algorithms. LZO provides the highest compression.
Create a compressed table:
We can use the following commands to create a table that uses HCC.
For Warehouse Compression:
1 2 | CREATE TABLE emp (…) COMPRESS FOR QUERY [LOW | HIGH]; |
For Archive Compression:
1 2 | CREATE TABLE emp (…) COMPRESS FOR ARCHIVE [LOW | HIGH]; |
Compressing an existing table with HCC:
We can use the following commands to compress an existing table.
Activate HCC:
1 2 | ALTER TABLE emp COMPRESS FOR QUERY [LOW | HIGH]; ALTER TABLE emp COMPRESS FOR ARCHIVE [LOW | HIGH]; |
If you want to perform compress, you can move the table with the following script.
Compress Table:
1 | ALTER TABLE emp MOVE; |
Activate HCC and Compress Table at the same time:
1 2 | ALTER TABLE emp MOVE COMPRESS FOR QUERY [LOW | HIGH]; ALTER TABLE emp MOVE COMPRESS FOR ARCHIVE [LOW | HIGH]; |
If the compression is done with the move, the indexes in the table will become unusable. These indexes need to be rebuild.
Deactivate HCC:
1 | ALTER TABLE emp NOCOMPRESS; |
Compression Advisor :
The Compression Advisor provides us to see the gains we can achieve with the HCC in databases that are not in Exadata hardware. It is possible to use the Compression Advisor with the plsql package called DBMS_COMPRESSION. To do so, the patch number 8896202 must be applied.
Suggestions:
HCC Compression is used while exporting tables that uses HCC. When you try to import such tables into a database that is not on the Exadata hardware, an ORA-64307 error will be received. These tables need to be converted for a successful import operation. To avoid problems, it is necessary to run the import with the “transform = segment_attributes: n: table” parameter.
HCC should not be used on tables that perform update, insert, delete operations too often.
The standby databases of the databases that uses HCC can also be on a system that supports HCC. The standby database of a database that uses HCC on Exadata cannot be created on a non-Exadata hardware.
You should not use HCC if you want to create a standby database that is not on Exadata hardware for a database running on Exadata.
