X

Database Storage Optimization best practices, tips and tricks and guidance from Database Compression Product Management

  • November 30, 2017

Row Level Compression with Advanced Row and Hybrid Columnar Compression (HCC)

Gregg Christman
Product Manager

In this blog we are going to discuss row level compression with Advanced Row Compression, and now available with Hybrid Columnar Compression (with Oracle Database 12.2), when using Automatic Data Optimization (ADO). In an earlier blog we discussed how Automatic Data Optimization and Heat Map automate the compression of data with Oracle Database 12c. (see here)

As was discussed in the earlier blog, with Oracle Database 12c implementing compression on existing tables/partitions can be automated using Automatic Data Optimization. Compression can be specified at the “row” level or the “segment” level. Row level (available only with Advanced Row Compression with Oracle Database 12.1) allows the table to be compressed in place, block-by-block, as all the rows on a block meet the ADO policy condition.

With Oracle Database 12.2 this functionality was enhanced to provide row-level support for Hybrid Columnar Compression – with HCC, rows that meet the ADO policy condition are re-located to a HCC Compression Unit. A Compression Unit (CU) is a logical construct used to store a set of hybrid columnar compressed rows.

Implementing an automated, row-level compression solution, using Automatic Data Optimization (and Heat Map) is straightforward. The ADO policy examples below enable Advanced Row Compression, and Hybrid Columnar Query Compression, using row-level compression when the ADO policy condition (as monitored by Heat Map) “AFTER 2 DAYS OF NO MODIFICATION” is met. The compression will occur during maintenance windows.

This ADO policy enables row-level Advanced Row Compression.

ALTER TABLE orders ILM ADD POLICY
ROW STORE COMPRESS ADVANCED ROW
AFTER 2 DAYS OF NO MODIFICATION;

This ADO policy enables row-level Hybrid Columnar Compression.

ALTER TABLE sales ILM ADD POLICY
COLUMN STORE COMPRESS FOR QUERY ROW
AFTER 2 DAYS OF NO MODIFICATION;

Tables/partitions can also be compressed at the segment level for both Advanced Row and Hybrid Columnar Compression, this means the entire segment is compressed at the same time.

The ADO policy example below enables segment-level Hybrid Columnar Compression.

ALTER TABLE orders ILM ADD POLICY
COLUMN STORE COMPRESS FOR ARCHIVE HIGH SEGMENT
AFTER 90 DAYS OF NO MODIFICATION;

It should be noted that while row level compression does allow you to compress the table/partition without rebuilding the entire segment, it does also mean that the entire segment may not compress for days, weeks or months depending on how long it takes for all rows to meet the ADO policy condition.

To compress the entire segment together, you would need to compress at the segment level, which would require rebuilding the table/segment in a compressed format.

For now, the database storage optimization adventure continues my next blog.

Join the discussion

Comments ( 2 )
  • Dhritiman Deb Wednesday, March 21, 2018
    I like the feature of row level compression by ADO policy but how do I check/verify which rows are compressed and compression ratio?
  • Gregg Christman Thursday, March 29, 2018
    You can run compression advisor (DBMS_COMPRESSION) on already compressed tables to estimate compression ratios. Please see the MOS note below for information about determining if rows are compressed:
    - How to see if rows are compressed in a table (Doc ID 1477918.1)
    - Exadata Hybrid Columnar Compression (EHCC) FAQ (Doc ID 1910687.1)
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.