Table Compression in Oracle 11g

Table compression was first introduced in Oracle 9i as a space saver feature only for data warehousing projects. In oracle 11g , this is acceptable as mainstream feature which is acceptable for OLTP Databases. This will result in saving storage space as this will compress the table, increased I/O performance and reduced memory use in the buffer cache. Definitely, this incurs CPU overhead because of compression.

Compression can be done at the tablespace, table or partition level . Options/Features which are available with Compression :

• NOCOMPRESS - The table or partition is not compressed. This is default without any compression.

• COMPRESS – suitable for OLAP environments i.e. for data warehouse systems. Compression is enabled on the table or partition during direct-path inserts only.

• COMPRESS FOR DIRECT_LOAD OPERATIONS – same as of simple “COMPRESS” keywords.

• COMPRESS FOR ALL OPERATIONS –  this is the new feature which is introduced in Oracle 11g  this is suitable for OLTP systems. This option will enable compressions for all operations  (including DML operations).

        Note -> this option is renamed as COMPRESS FOR OLTP in Oracle 11gR2.

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

-- Table compression.
CREATE TABLE test_compress (
  id            NUMBER(10)    NOT NULL,
  desc1        VARCHAR2(50)  NOT NULL,
  created_tstp  DATE          NOT NULL
)
COMPRESS FOR ALL OPERATIONS;

-- Partition-level compression.
CREATE TABLE test_compress_par (
  id            NUMBER(10)    NOT NULL,
  desc1        VARCHAR2(50)  NOT NULL,
  created_tstp  DATE          NOT NULL
)
PARTITION BY RANGE (created_tstp) (
  PARTITION test_compress_par1 VALUES LESS THAN (TO_DATE('01/01/2013', 'DD/MM/YYYY')) COMPRESS,
  PARTITION test_compress_par2 VALUES LESS THAN (TO_DATE('01/05/2013', 'DD/MM/YYYY')) COMPRESS FOR ALL OPERATIONS,
  PARTITION test_compress_par3 VALUES LESS THAN (TO_DATE('01/09/2013', 'DD/MM/YYYY')) COMPRESS FOR DIRECT_LOAD OPERATIONS,
  PARTITION test_compress_par4 VALUES LESS THAN (MAXVALUE) NOCOMPRESS
);

Lets have a look at the setting in dba_tables:

SELECT table_name, compression, compress_for
  FROM dba_tables
 WHERE table_name IN ('TEST_COMPRESS', 'TEST_COMPRESS_PAR')

   

TABLE_NAME	COMPRESSION	COMPRESS_FOR
TEST_COMPRESS	ENABLED	OLTP
TEST_COMPRESS_PAR

Since for table “TEST_COMPRESS_PAR” compression is done at Pratition level, hence its Null in dba_objects.

Lets have a look at the details at partition level:

TABLE_NAME	COMPRESSION	COMPRESS_FOR
TEST_COMPRESS_PAR	ENABLED	BASIC
TEST_COMPRESS_PAR	ENABLED	BASIC
TEST_COMPRESS_PAR	DISABLED
TEST_COMPRESS_PAR	ENABLED	OLTP

How to Alter compression :

Compressions can be altered on the table using ALTER table command.

ALTER TABLE TEST_COMPRESS NOCOMPRESS;

ALTER TABLE test_compress_par MODIFY PARTITION test_compress_par4 COMPRESS FOR ALL OPERATIONS;

Partition compression will always override compression done at table level.

Interesting points :

• Compressed tables can only have columns added or dropped if the COMPRESS FOR ALL OPERATIONS option was used.
• Compression cannot applied to lob segments.
• Table compression is only valid for heap organized tables.
• Table compression is not applied to index organized tables.
• The compression clause cannot be applied to hash or hash-list partitions. 
• Table compression cannot be specified for external or clustered tables.