About Oracle Hybrid Columnar Compression (11.2)

By Richard Niemiec on June 17, 2013


Hybrid Columnar Compression (HCC), also known as Exadata Hybrid Columnar Compression (EHCC), is data that is organized by a hybrid of columns/rows and compressed versus data organized by basic row format. A performance gain anywhere from 10×to 30× is common. The tables are organized in compression units (CU), which contain around 1000 rows (more or less depending on the amount of data in the rows). CUs span many blocks. HCC is very good for bulk-loaded data, but is not built for OLTP or single block read operations. It is primarily built for data warehouses and queried data, not for frequently updated data.

Using the old OLTP compression algorithm, you could get 2–3× compression or so. Yet, with HCC, 10× compression in a typical data warehouse is common. In limited tests, I got anywhere from 4–11×. HCC also allows archive compression (used for cold data), which gives you anywhere from 15× to 70× compression (I got approximately 32× compression in limited tests). The nice thing about hybrid columnar compression is that operations are faster because the query runs without decompression. The compressed version is processed in the Flash Cache, which results in lower I/O. The compressed version is also sent over InfiniBand; it is cloned compressed; and it is even backed up compressed! As a result, it scans much less (compressed) data!

Note that you can still use standard table compression for OLTP, and a single block lookup is still generally faster than other columnar storage. The updated rows migrate to normal/lower-level compression. HCC fully supports:

  • B-tree indexes
  • Bitmap indexes
  • Text indexes
  • Materialized views
  • Partitioning
  • Parallel queries
  • Data guard physical standby
  • Logical standby and Streams (in a future release)
  • Smart Scans of HCC tables!

Don’t forget the many other types of Oracle compression that also give Oracle an advantage over the competition:

Data Pump compression Compression ={ALL|DATA_ONLY |NONE}
RMAN Backup compression Compression LOW/HIGH (new in 11.2)
Secure File compression (large objects/images) LOW/MEDIUM/HIGH (2–3× compression) Deduplication and encryption
Normal OLTP table compression (since 9.2) 11g now supports INSERT/UPDATE FASTER algorithm
Data Guard Redo Transport compression See My Oracle Support Doc ID 729551.1 for information using the initialization parameter _REDO_TRANSPORT_COMPRESS_ALL

?

Related Posts

Leave a Reply