Hybrid Columnar Compression

Hybrid Columnar Compression (HCC) is a feature that comes with Oracle Database 11g Release 2, which can be used with Exadata storage, Pillar Axiom storage systems and Sun ZFS storage appliance.

It is possible to compress tables from 10 to 50 times using HCC. In tables using HCC, the data is kept in the column to allow better compression and deduplication. The following picture shows the difference.

 

Keeping data in the form of column sorting normally has a negative effect on performance. However, with the HCC feature, the tables are divided into pieces and this disadvantage has been turned into a very serious advantage and it provides a very serious gain in i / o processes. These separated parts are called compression units. Each compression unit contains a subset of the rows and is arranged in column order. Compression units are larger than database blocks. The CU size ranges from 32K to 256K. It is generally around 32K.

Types of Hybrid Columnar Compression :

There are two types of HCC.

  1. Warehouse Compression : It aims to achieve a reduction in storage space with an increase in performance. Optimized for speed. It allows data to take up to 10 times less space on the disk. I / O for scanning also provides up to a 10-fold reduction. Optimized for Smart Scan feature.
  2. Archive Compression : It is optimized to make more compression. It can save 15 to 50 times less storage space. Ideal for tables with historical data.

It is possible to use LOW and HIGH, which determine the level of compression for both types.

Compression algorithms such as LZO, ZLIB, and BZ2 are used with HCC. Of these, LZO provides the highest compression.

Creating a compressed table:

We can use the following commands to create a table using HCC.

For Warehouse Compression:

CREATE TABLE emp (…) COMPRESS FOR QUERY [LOW | HIGH];

For Archive Compression:

CREATE TABLE emp (…) COMPRESS FOR ARCHIVE [LOW | HIGH];

Alter a table for compression :

It is also possible to compress a non-HCC table later. We can use the following commands for this.

To activate HCC compensation in a non-HCC table:

ALTER TABLE emp COMPRESS FOR QUERY [LOW | HIGH]; 
ALTER TABLE emp COMPRESS FOR ARCHIVE [LOW | HIGH];

If compression is desired, then the table should be moved as follows.

ALTER TABLE emp MOVE;

To enable and compress compression with HCC in a non-HCC table:

ALTER TABLE emp MOVE COMPRESS FOR QUERY [LOW | HIGH];
ALTER TABLE emp MOVE COMPRESS FOR ARCHIVE [LOW | HIGH];

If a table is moved, the indexes in the table will become unusable. These indices need to be rebuilded.

Turning off HCC compression in a table:

We can close the HCC compression process in a table with the command below.

ALTER TABLE emp NOCOMPRESS;

Compression Advisor :

It allows us to see how much compression we can do with HCC in databases that are not on Exadata hardware with Compression Advisor. It is possible to use DBMS_COMPRESSION with plsql package. For this, patch 8896202 should be applied.

Suggestions :

When the tables using HCC are exported, they are again imported with HCC compression. The ORA-64307 error will be received when you try to import such tables into a database on a non-Exadata hardware. These tables must be converted and imported for smooth import. In order to avoid problems, it is necessary to run the import with the parameter transform=segment_attributes:n:table
HCC should not be used in transactions with update insert delete frequently.
Since standby databases of databases with HCC will also be in databases that can be HCC, standby databases cannot be created on a non-Exadata hardware of a database using HCC on Exadata. For this, you should not use the HCC feature on the Exadata side.

 

Please follow and like us:

Leave a Reply

Your email address will not be published. Required fields are marked *