Primary Note for OLTP Compression (Doc ID 1223705.1)

Last updated on JUNE 13, 2023

Applies to:

Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Cloud Schema Service - Version N/A and later
Information in this document applies to any platform.

Purpose

This document describes the 11g new feature OLTP Compression and provides usage guidelines that help to maximize compression ratio and performance.

Scope

The Oracle11g Advanced Compression Option includes OLTP Compression, SecureFiles Deduplication and Compression (LOBs), Recovery Manager Compression, Data Pump Compression, and DataGuard redo compression. This document is focused solely on OLTP Compression which is one of several types of heap segment compression (HSC), the other features of the Advanced Compression Option as listed above are not covered here. As it is important to share common terminology for this topic an excerpt from the manual is provided below for clarity.

    * When you enable table compression by specifying either COMPRESS or COMPRESS BASIC, you enable basic table compression. Oracle Database attempts to compress data during direct-path INSERT operations when it is productive to do so. The original import utility (imp) does not support direct-path INSERT, and therefore cannot import data in a compressed format.

      In earlier releases, this type of compression was called DSS table compression and was enabled using COMPRESS FOR DIRECT_LOAD OPERATIONS. This syntax has been deprecated.

      See Also: "Conventional and Direct-Path INSERT" for information on direct-path INSERT operations, including restrictions

    * When you enable table compression by specifying COMPRESS FOR OLTP, you enable OLTP table compression. Oracle Database compresses data during all DML operations on the table. This form of compression is recommended for OLTP environments.

      In earlier releases, OLTP table compression was enabled using COMPRESS FOR ALL OPERATIONS. This syntax has been deprecated.

      Note: Tables with COMPRESS or COMPRESS BASIC use a PCTFREE value of 0 to maximize compression, unless you explicitly set a value for PCTFREE in the physical_attributes_clause. Tables with COMPRESS FOR OLTP or NOCOMPRESS use the PCTFREE default value of 10, to maximize compress while still allowing for some future DML changes to the data, unless you override this default explicitly.

    * When you specify COMPRESS FOR QUERY or COMPRESS FOR ARCHIVE, you enable hybrid columnar compression. With hybrid columnar compression, data can be compressed during bulk load operations. During the load process, data is transformed into a column-oriented format and then compressed. Oracle Database uses a compression algorithm appropriate for the level you specify. In general, the higher the level, the greater the compression ratio.

Hybrid columnar compression can result in higher compression ratios, at a greater CPU cost. Therefore, this form of compression is recommended for data that is not frequently updated.

COMPRESS FOR QUERY is useful in data warehousing environments. Valid values are LOW and HIGH, with HIGH providing a higher compression ratio. The default is HIGH.

COMPRESS FOR ARCHIVE uses higher compression ratios than COMPRESS FOR QUERY, and is useful for compressing data that will be stored for long periods of time. Valid values are LOW and HIGH, with HIGH providing the highest possible compression ratio. The default is LOW.

See Also: Oracle Exadata Storage Server Software documentation for more information on hybrid columnar compression, which is a feature of Oracle Exadata.

Note that Hybrid Columnar Compression has specific requirements for underlying storage including Exadata, ZFS, or Pillar.

If a table contains LOB columns then it is possible to compress table only without compressing the LOB data. Compressing securefile columns have different syntax, syntax that specifiles compression within the LOB clause. See more about this at http://docs.oracle.com/cd/E11882_01/appdev.112/e18294/adlob_smart.htm#ADLOB45960

Basic and Advanced compression are installed by default without the need of explicitly selecting the component for install during the installation of the Oracle Database Server, or creation of the database.

NOTE: Currently Advanced Compression cannot be disabled. Enhancement has been logged to request the possibility to disable/un-install the option. For more details see Document 1459216.1.

From a licensing point of view, the Feature Usage is the deciding factor for "Advanced Compression". It is sufficient to document that you are not using this feature.