Oracle 8i | Oracle 9i | Oracle 10g | Oracle 11g | Oracle 12c | Miscellaneous | PL/SQL | SQL | Oracle RAC | Oracle Apps | Linux

Table Compression Enhancements in Oracle Database 11g Release 1

Table compression was introduced in Oracle 9i as a space saving feature for data warehousing projects. In 11g it is now considered a mainstream feature that is acceptable for OLTP databases. In addition to saving storage space, compression can result in increased I/O performance and reduced memory use in the buffer cache. These advantages do come at a cost, since compression incurs a CPU overhead, so it won't be of benefit to everyone.

Note. Basic table compression is a free feature of the Enterprise Edition database, but OLTP compression requires the Advanced Compression option.

The compression clause can be specified at the tablespace, table or partition level with the following options:

The following examples show the various compression options applied at table and partition level.

-- Table compression.
CREATE TABLE test_tab_1 (
  id            NUMBER(10)    NOT NULL,
  description   VARCHAR2(50)  NOT NULL,
  created_date  DATE          NOT NULL
)
COMPRESS FOR ALL OPERATIONS;

-- Partition-level compression.
CREATE TABLE test_tab_2 (
  id            NUMBER(10)    NOT NULL,
  description   VARCHAR2(50)  NOT NULL,
  created_date  DATE          NOT NULL
)
PARTITION BY RANGE (created_date) (
  PARTITION test_tab_q1 VALUES LESS THAN (TO_DATE('01/01/2008', 'DD/MM/YYYY')) COMPRESS,
  PARTITION test_tab_q2 VALUES LESS THAN (TO_DATE('01/04/2008', 'DD/MM/YYYY')) COMPRESS FOR DIRECT_LOAD OPERATIONS,
  PARTITION test_tab_q3 VALUES LESS THAN (TO_DATE('01/07/2008', 'DD/MM/YYYY')) COMPRESS FOR ALL OPERATIONS,
  PARTITION test_tab_q4 VALUES LESS THAN (MAXVALUE) NOCOMPRESS
);

Table-level compression settings are reflected in the COMPRESSION and COMPRESS_FOR columns of the [DBA|ALL|USER]_TABLES views.

SELECT table_name, compression, compress_for FROM user_tables;

TABLE_NAME                     COMPRESS COMPRESS_FOR
------------------------------ -------- ------------------
TEST_TAB_1                     ENABLED  FOR ALL OPERATIONS
TEST_TAB_2

2 rows selected.

SQL>

Tables defined with partition-level compression and no table-level compression display NULL values in these columns.

Partition-level compression settings are reflected in the COMPRESSION and COMPRESS_FOR columns of the [DBA|ALL|USER]_TAB_PARTITIONS views.

SELECT table_name, partition_name, compression, compress_for FROM user_tab_partitions;

TABLE_NAME                     PARTITION_NAME                 COMPRESS COMPRESS_FOR
------------------------------ ------------------------------ -------- ------------------
TEST_TAB_2                     TEST_TAB_Q1                    ENABLED  DIRECT LOAD ONLY
TEST_TAB_2                     TEST_TAB_Q2                    ENABLED  DIRECT LOAD ONLY
TEST_TAB_2                     TEST_TAB_Q3                    ENABLED  FOR ALL OPERATIONS
TEST_TAB_2                     TEST_TAB_Q4                    DISABLED

4 rows selected.

SQL>

The compression settings for tables and partitions can be modified using the ALTER TABLE command. The alterations have no effect on existing data, only on new operations applied to the table.

ALTER TABLE test_tab_1 NOCOMPRESS;

ALTER TABLE test_tab_2 MODIFY PARTITION test_tab_q4 COMPRESS FOR ALL OPERATIONS;

Default compression settings can be specified at the tablespace level using the CREATE TABLESPACE and ALTER TABLESPACE commands. The current settings are displayed in the DEF_TAB_COMPRESSION and COMPRESS_FOR columns of the DBA_TABLESPACES view.

CREATE TABLESPACE test_ts
  DATAFILE '/u01/app/oracle/oradata/DB11G/test_ts01.dbf'
  SIZE 1M
  DEFAULT COMPRESS FOR ALL OPERATIONS;

SELECT def_tab_compression, compress_for
FROM   dba_tablespaces
WHERE  tablespace_name = 'TEST_TS';

DEF_TAB_ COMPRESS_FOR
-------- ------------------
ENABLED  FOR ALL OPERATIONS

1 row selected.

SQL>

ALTER TABLESPACE test_ts DEFAULT NOCOMPRESS;

SELECT def_tab_compression, compress_for
FROM   dba_tablespaces
WHERE  tablespace_name = 'TEST_TS';

DEF_TAB_ COMPRESS_FOR
-------- ------------------
DISABLED

1 row selected.

SQL>

DROP TABLESPACE test_ts INCLUDING CONTENTS AND DATAFILES;

When compression is specified at multiple levels, the most specific setting is always used. As such, partition settings always override table settings, which always override tablespace settings.

The restrictions associated with table compression include:

For more information see:

Hope this helps. Regards Tim...

Back to the Top.