Hybrid Columnar Compression ( HCC ) in Oracle Exadata -1

Hi,

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

 

Read first article of Hybrid Columnar Compression before this like following.

 

 

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.

 

 

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

 

Mehmet Salih Deveci

I am Founder of IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress mehmetsalih.deveci@outlook.com.-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  mehmetsalih.deveci@outlook.com a mail atabilirsiniz.

Leave a Reply

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