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