Oracle Advanced Compression — lower cost, less I/O

Oracle Advanced Compression can reduce storage requirements dramatically and therefore saves I/O, without placing higher demands on implementation or operation. Storage is typically reduced to around 25 percent; on Exadata machines the requirement can drop by up to 90 percent.

Overview

With the introduction of Advanced Compression in 11g, the compression functionality already known from earlier versions gained substantial improvements and extensions. Two differences are of particular interest:

LOB compression

Compressing LOB columns became possible for the first time. The storage parameters of the LOBs have to be adjusted accordingly (SecureFile, compress high).

Update handling

The way compressed blocks are handled on update was reworked substantially.

In practice, Oracle's compression features receive less attention than they deserve. In VLDB environments an overall reduction in storage of roughly 50 percent is achievable, and individual large tables can compress to 25 percent of their original size. That is a considerable saving in cost, and it also lowers I/O requirements because fewer blocks have to be read.

Compression levels

Basic compression

COMPRESS FOR DIRECT_LOAD OPERATIONS: this feature carries no licence cost and is the compression already known from Oracle 10g.

Oracle Advanced Compression

COMPRESS FOR ALL OPERATIONS or COMPRESS FOR OLTP: this enables Oracle Advanced Compression and requires a licence.

Oracle Exadata Hybrid Columnar Compression

COMPRESS FOR ARCHIVE LOW|HIGH: this also requires a licence and achieves a significantly better compression ratio, but is currently available on Exadata machines only.

Example of a compressed table with CLOBs

In the following script the table is compressed with compress for all operations. Reductions of around 75 percent are typical here.

CREATE TABLE COMPRESS_TEST
(
  ID                    NUMBER,
  COMPRESS_TEST        VARCHAR2(32),
  MESSAGE               CLOB
)
LOB (MESSAGE) STORE AS SECUREFILE (
  TABLESPACE  DATA
  ENABLE      STORAGE IN ROW
  COMPRESS    HIGH)
compress for OLTP;