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

When to Use the Various Types of Oracle Data Compression

Gregg Christman
Product Manager

In this blog we are going to discuss the various types of data compression, the differences between the compression types, and when to use (and not use) the different data compression types.

Basic Table Compression

If you are not familiar with Basic Table Compression, then some important points to know about Basic Table Compression are that it is a free data compression capability and it is included with Oracle Database Enterprise Edition. Oracle Database 9i Release 2 introduced Basic Table Compression, which compresses data that is loaded using bulk load operations, but does not compress data that is added/changed through conventional DML operations (INSERT or UPDATE). If DML INSERTS and UPDATES are performed on a Basic compressed table/partition over time, then that table/partition would have to be re-compressed to get the changes compressed.

USAGE: Basic Table Compression isn’t intended for OLTP applications, and instead, is best suited for data warehouse applications (read-mostly) where data is loaded using bulk load operations and is never (or very rarely) modified.

Advanced Row Compression

Oracle Database 11g Release 1 introduced OLTP Table Compression, now called Advanced Row Compression with Oracle Database 12c and above. Advanced Row Compression is the data compression feature of Advanced Compression that uses the same algorithm as Basic Compression, but differs from Basic Compression in that Advanced Row Compression maintains data compression during all types of data manipulation operations, including conventional DML such as INSERT and UPDATE.

Advanced Row Compression uses a compression algorithm specifically designed to eliminate duplicate values within a database block, even across multiple columns. The compression ratio achieved in a given environment (also true for Basic Compression) depends on the data being compressed, specifically the cardinality of the data.

In general, organizations typically see a compression ratio in the range of 2x to 4x when using Advanced Row Compression (Basic compression produces similar compression ratios). That is, the amount of space consumed by uncompressed data will be two to four times larger than that of the compressed data.

USAGE: Advanced Row Compression is intended for both OLTP and Data Warehouse applications.

Hybrid Columnar Compression

Unlike both Basic and Advanced Row Compression, Oracle’s Hybrid Columnar Compression 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 (CU) 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. To maximize storage savings with Hybrid Columnar Compression, data must be loaded using bulk loading (direct path) techniques.

Examples of bulk load operations commonly used includes: Insert statements with the APPEND hint, Parallel DML, Direct Path SQL*LDR and/or Create Table as Select (CTAS). In general, organizations can typically expect compression ratios in the range of 6x to 15x when using Hybrid Columnar Compression.

USAGE: Hybrid Columnar Compression is best suited for data warehouse applications (read-mostly) where data is loaded using bulk load operations and is never (or very rarely) modified.

More information about Oracle data compression (including best practices):

Advanced Row Compression White Paper

Hybrid Columnar Compression White Paper

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.