One of the focus areas of the Marlabs’ BI Center of Excellence (CoE) is Big Data and helping customers harness its huge potential. To that end, this is the second post of the multi-part series on the highlights of Oracle Exadata with specific attention on how these “appliances” differ from traditional Relational Database Management System (RDBMS). In part 1 of the series we examined Exadata’s SMART SCAN feature. Now let’s look at look at Exadata’s Exadata Hybrid Columnar Compression (EHCC).
A salient features of Exadata is the new Compression technique it uses – Exadata Hybrid Columnar Compression (EHCC).
Overview of Hybrid Columnar Compression Technology
Traditionally, data has been organized within a database block in a ‘row’ format, where all column data for a particular row is stored sequentially within a single database block. Having data from columns with different data types stored close together limits the amount of storage savings achievable with compression technology.
An alternative approach is to store data in a ‘columnar’ format, where data is organized and stored by column. Storing column data together, with the same data type and similar characteristics, dramatically increases the storage savings achieved from compression. However, storing data in this manner can negatively impact database performance when application queries access more than one or two columns, perform even a modest number of updates, or insert small numbers of rows per transaction.
Oracle’s Hybrid Columnar Compression technology is a new method for organizing data within a database block. As the name implies, this technology utilizes a combination of both row and columnar methods for storing data. This hybrid approach achieves the compression benefits of columnar storage, while avoiding the performance shortfalls of a pure columnar format.
A logical construct called the compression unit is used to store a set of hybrid columnar compressed rows. When data is loaded, column values for a set of rows are grouped together and compressed. After the column data for a set of rows has been compressed, it is stored in a compression unit.
(Courtesy: Oracle white paper on Exadata published on Oct 2011)
Benefits of Hybrid Columnar Compression
Storage Saving – Exadata Hybrid columnar compression introduces a new physical storage, the concept compression unit, which provides the highest levels of data compression. By grouping many rows together in a compression unit and storing only unique values within each column, EHCC provides storage savings in the range of 10 – 50% based on the compression level selected. EHCC has two compression modes and this makes it very useful for data warehousing.
- Query Mode – Up to 10x compression using Exadata Hybrid Columnar Compression. This can mode can be implemented for Active Partitions.
- Archive mode – Up to 50x compressing using Exadata Hybrid Columnar Compression. This mode can be implemented for Archive Partitions.
Combining EHCC feature with Oracle partitioning provides the ability to divide a single table into smaller partitions with different compression rule. This provides an ideal solution for keeping more data online by having recent data in partitions with Query Mode compression and data which are qualified for archive to be moved into partitions with Archive mode compression.This new compression technique enables organizations to make data available for significantly longer periods of time while reducing the extra storage cost.
Increased Performance – Since data from full table scans remains compressed through I/O and buffer cache layers, disk savings translate to reduced I/O and buffer cache work as well. This means a table that achieves a 10x (10:1) compression ratio will typically see a 10x (10:1) reduction in I/O for scan-oriented queries.
