Oracle Database Compression Technologies

With the latest versions of standalone Oracle Database 11gR2, either RAC or single instance, and Oracle Exadata Machine version v2 and x2, we have several data compression alternatives according to our customers requirements. As you all know, cost of maintaining their live production data and their historical Data Warehouse or DSS (Decision Support System) data is an increasing expense for IT department. On the other hand, analyzing and reaching to the older data is an indispensable requirement for all those companies. As an information company, Oracle’s strategy for decreasing the cost of and also improving the performance of accessing either production or historical data is evolving for many years now reaching maturity levels no other companies can claim. It started with DSS type data warehousing compression and now we have the chance of compressing even the live transactional tables or partition in our customers Oracle databases. Within the early years of 2000, Oracle started to present a solid Information Life Cycle to its customers, with mainly solves their requirement of moving less accessed data to less expensive storage.

What customer would say NO! to a reduction in their production database size?

In 2007, with the Oracle database version 11gR1, Oracle released “Advanced Compression Option” which includes not only OLTP Data compression, but also backup – archivelog (for standby db ) and export dumpfile compression.

Imagine you have 600GB of production database size and it is possible with “Advanced Compression OLTP table Compression” feature to reduce it into 250 – 300 GBs. Depending on the data content, compression ratios of 2x to 4x is possible with this technology.

Gains of OLTP compression?

- Reduced Live OLTP data size

- Reduced Backup Size

- Reduced Disaster or Standby database size

- Reduced Export Dump size

- Reduced disk I/O while reading data blocks ( without overhead while reading )

- Reduced network traffic while sending archivelogs to DR site.

Most of the people, especially the technical ones are asking the overhead of this feature in terms of performance. Naturally, getting this question is expected. Oracle built a new batch algorithm to minimize the impact of writes to the OLTP compressed tables.

Sample syntax for OLTP Compressed table creation:

CREATE TABLE emp (

emp_id NUMBER,

first_name VARCHAR2(128),

last_name VARCHAR2(128))

COMPRESS FOR ALL OPERATIONS;

What About Historical Data?

Expecially, with Oracle Exadata Machine solution, choices to solve the problem of storing and maintaining huge sized data increased. In addition to Oracle’s traditional DSS type data warehousing compression algorithm, we have now incomparable Exadata compression technologies for historical data.

Let’s remember the syntax for the tradional compression type for DSS(decision support systems) Tables:

CREATE TABLE old_data ... COMPRESS BASIC;

CREATE TABLE old_data ... COMPRESS;

Data blocks of the table compressed with COMPRESS or COMPRESS BASIC commands ( which is usable since Enterprise Edition 10g onwards ) will continue to be compressed, but for the new records and data blocks to be compressed the should be inserted with the following ways.

Direct-path insert methods:

  • Direct path sqlloader
  • CTAS - CREATE TABLE AS SELECT statements
  • Parallel INSERT statements
  • INSERT statements with an APPEND hint

The Oracle Database built-in basic compression methods are extended through the Advanced Compression Option and the OLTP compression type that was explained earlier.

One of the unique feature of Oracle Exadata – Hybrid Columnar Compression

With the version of 11gR2 but just running on Oracle Exadata, we have a new technology for compressing data additional to BASIC and COMPRESS FOR ALL (oltp) options.

Before giving the details and benefits of Exadata Hybrid Columnar Compression, lets briefly mention about Hybrid Columnar Compression approach.

As all of us knows, Oracle keeps data inside data blocks in a row format. Since all the data in a row stored sequencially, different data types stays next to each other which limits to achive high compression ratios. With Hybrid Columnar compression technology, Oracle started to keep same column values of different rows in same data block.

This approach has a disadvange of reading a single row or a few row in a huge table. To avoid that performance problem, Oracle used a new logical “Compression Unit” to combine same rows columns into same compression unit block. Which enables to get all the data of a row in a single block i/o. There are 2 Compression Types specific to Exadata Hybrid Columnar Compression, one of those aimed performance over compressed tables, and the other one aimed to compress as much as possible to get the most storage capacity back.

Names of those technologies are Warehouse Compression and Archive Compression.

Warehouse Compression:

- Compression ratios of 10x achievable on average

- 10x reduction in scan i/o

- Optimized for speed

Exadata Hybrid Columnar Compression Warehouse Compression Syntax:

CREATE TABLE emp (

emp_id NUMBER ,

first_name VARCHAR2(128) ,

last_name VARCHAR2(128) )

COMPRESS FOR QUERY [ LOW | HIGH ];

Archive Compression:

- Compression ratios of 15x achievable on average

- For cold or historical data

- Optimized for Space

Exadata Hybrid Columnar Compression Archive Compression Syntax:

CREATE TABLE emp (

emp_id NUMBER ,

first_name VARCHAR2(128),

last_name VARCHAR2(128))

COMPRESS FOR ARCHIVE [ LOW | HIGH ];

Oracle Exadata Hybrid Columnar Compression (Flipchart video explaining new hybrid columnar compression technology)

Few key points for those technologies:

- Decompression of data with EHCC technologies offloaded to Exadata Storage Cells. So minimum overhead for database servers interms of CPU and memory.

- Only the columns and rows queried by the client ar decompressed in memory.

- OLTP compression is part of Advanced Compression option and requires Enterprise edition and Advanced Compression option license

- Basic compression is free with Enterprise Edition DB.

- EHCC features are free with Exadata J

Additional key points for Large Objects:

Well. Starting with 11g Release 1, Oracle gave us the chance of storing Large Objects (images, spreadsheets, xml files..) in "Secure Files" format. ( Oracle SecureFiles )

Instead of keeping the unstructured data outside the database, it is now possible to save them inside the Oracle Database in a secure, reliable, high available manner plus having the exceeding performance of a file system. ( SecureFiles Benchmark Results )

SecureFiles is a new feature of 11g database version of Oracle and it is free with Standard and Enterprise Edition license. This makes meaningful to save or old basicfiles and LOBs in securefile format.

If you have an Enterprise Edition database, plus having the license of Advanced Compression Option ( we tried to summarize the benefits above ) Securefiles enables us to De-duplicate and Compress our Large Objects. This capability reduces the amount of required storage capacity significantly. 

CREATE TABLE t1 ( a CLOB)

LOB(a) STORE AS SECUREFILE (

COMPRESS HIGH

         CACHE

   );


CREATE TABLE t1 ( REGION VARCHAR2(20), a BLOB) 

 LOB(a) STORE AS SECUREFILE (

        CACHE

     )

     PARTITION BY LIST (REGION) (

          PARTITION p1 VALUES ('x', 'y')

LOB(a) STORE AS SECUREFILE (

COMPRESS

                ),

          PARTITION p2 VALUES (DEFAULT)

     );

CREATE TABLE t1 ( a CLOB)

LOB(a) STORE AS SECUREFILE (

DEDUPLICATE

        CACHE

    );

Comments:

Sezgi, thank you very much for sharing.
Best regards.

Posted by Eser O. on August 11, 2011 at 01:45 PM CEST #

Sezgi, thank you very much for sharing.
Best regards.

Posted by Eser O. on August 11, 2011 at 01:46 PM CEST #

Hi,

What about large objects, can we compress our LOBs?

Thanks..

Posted by guest on August 24, 2011 at 09:06 PM CEST #

Thank you for your sharing.
I have a question.
I think "Exadata Hybrid Columnar Compression Archive Compression Syntax" is not "COMPRESS FOR QUERY" but "COMPRESS FOR ARCHIVE". Is it typo?

Posted by Akihiro on August 29, 2011 at 05:16 AM CEST #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Oracle ECEMEA Partner Hubs Migration Center Team

We share our skills to maximize your revenue!
Our dedicated team of consultants can rapidly and successfully assist you to adopt and implement the latest of Oracle Technology in your solutions.

Stay Connected
partner.imc
@
beehiveonline.oracle-DOT-com
Google+

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
2
3
4
5
6
9
10
11
12
13
14
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today