Hybrid Columnar Compression in Oracle R2

Hybrid Columnar Compression (Release 2 Only on Oracle Exadata) :

In 11g Release 2 (but only if pre-installed with Oracle Exadata v2), compression technology has been extended in the form of Hybrid Columnar Compression (HCC).

First of all, why there is a need to extend compression capability? The reason is simple: not all data in a database may be searched in the same way. For example, some data (such as company email) needs to be in the database purely for legal reasons; it must be accessible all the time, even if actually needed only rarely. This data needs to be stored, and storage costs money – for the disks, for power to run the disks, for cooling, for floorspace. So why use expensive storage for data that is rarely, if ever, accessed?

This is where HCC comes in. Typically compression works by replacing repeating values with some smaller symbols and reducing overall space consumption. For instance, suppose the rows of uncompressed data looks like this (with columns separated by “|”):

Row1: Quite_a_large_value1|Quite_a_long_value1|Another_quite_long_value1 
Row2: Quite_a_large_value2|Quite_a_long_value1|Another_quite_long_value1 
Row3: Quite_a_large_value1|Quite_a_long_value2|Another_quite_long_value1 
Row4: Quite_a_large_value1|Quite_a_long_value1|Another_quite_long_value2

The total size of the three rows is 264 bytes. Note that there are actually just six distinct values in these three rows, which have been used several times.

Quite_a_large_value1 Quite_a_large_value2 Quite_a_long_value1 Quite_a_long_value2 Another_quite_long_value1 Another_quite_long_value2

When this block is compressed, a special value is assigned to each distinct value and the actual values are replaced by the corresponding special values. For instance, here are the assigned special values:

---

Original Value	Replaced by Symbol
Quite_a_large_value1	A1
Quite_a_large_value2	A2
Quite_a_long_value1	B1
Quite_a_long_value2	B2
Another_quite_long_value1	C1
Another_quite_long_value2	C2

---

The rows now look like:

 Row1: A1|B1|C1 
 Row1: A2|B1|C1 
 Row1: A1|B2|C1
 Row1: A1|B1|C2

Total size: 32 bytes, a substantial reduction from 264, by about 88%. (Of course the percentage of reduction will depend on the data, in particular how many distinct values are present, but the principle is the same.) The relationship between unique symbols (A1, A2, etc.) and the values they represent (“Quite_a_large_value1”, etc.) is stored in a structure called Symbol Directory and is stored in the block header. Each block header must store the symbols used in that block. Naturally, there will be repetitions since the same values will be repeated in the blocks.

If you look at a real life data, you will notice that the values are typically repeated across columns, not rows. For instance, a column called FIRST_NAME will have values like Airy, Pawan, etc. while another called CITY_NAME will have values like Kanpur or New Delhi. You will not expect to see a value called New Delhi on the first_name column. Since values repeat more frequently within the columns, rather than rows, you can have just one symbol directory per column. Since symbol directories take up space, the significant reduction in their numbers will reduce the overall space compared to traditional compression where every block must have a symbol directory.

Hybrid Columnar Compression uses this method. This accomplishes significant amount of compression but affects the DML performance. So, it is best used for tables that are queries that are not subject to DML.

Here is an example of a table that is created as

create table trans_comp nologging compress for query low as select * from trans;

The clause “compress” compresses the table. The clause “for query” compresses it in hybrid columnar manner. The clause “low” does not compress it as aggressively. So more space is consumed but compression and decompression do not consume as much CPU. A more aggressive compression can be achieved by replacing the “low” clause with “high”. This type of HCC is known as warehouse compression since it is useful for data warehouses where a lot of data is stored but they are queried frequently.

If a table will be accessed very rarely, you can compress it even more, using a “for archive” clause:

create table trans_comp nologging compress for archive low as select * from trans;

This will reduce the space consumption even more but at the cost of CPU. Like the “for query” clause’s setting it also has two values, high and low. This is known as archive compression where data is accessed infrequently. Here is an example of the compression ratios on a set of representative data. Of course they will vary widely with the data.

---

Type of Compression	Compressed Table Size
Uncompressed	100.00%
Query Low	14.70%
Query High	8.82%
Archive Low	6.62%
Archive High	4.41%

---

Hybrid Columnar Compression allows you to compress tables that are not subject to too much DML but whose data can’t be dropped for legal or other reasons. Now you can store these tables yet consume far less storage, and possibly use less CPU as well. Note again that this feature is available only on Oracle Exadata Storage Server v2.

Thank you for reading… This is Airy…Enjoy Learning 🙂