This article discusses Oracle Table Compression features and usage. Databases are getting bigger and bigger over time and demand more disk-space for storage. Archived data, which is mostly read-only and used for reporting in Warehouses and even on OLTP systems, is stored in compressed form as a best practice. Compressed data can increase I/O performance and reduced memory use in buffer cache; however, it can also increase CPU usage.

The following compression options are available in Oracle 11g:

  • NOCOMPRESS: The default where table and partition data is not compressed.
  • COMPRESS: Suitable for data warehouse databases. Compression is enabled on table and partition data during direct-path inserts.
  • COMPRESS FOR DIRECT_LOAD OPERATIONS: Same as COMPRESS.
  • COMPRESS FOR ALL OPERATIONS: Suitable for OLTP databases. Compression is enabled for all operations, including DML statements. It requires COMPATIBILE initialization parameter set to 11.1.0 or higher. In 11gR2 this option has been renamed to COMPRESS FOR OLTP. Although original name is deprecated but still works in 11.2.0.3

Example of Table level Compression

-- compressed table
SQL> CREATE TABLE table_comp_test(
  2   anumber NUMBER(10),
  3   avarchar2 VARCHAR2(100),
  4   adate DATE)
  5 COMPRESS FOR ALL OPERATIONS;

Table created.

-- uncompressed table
SQL> CREATE TABLE table_test(
  2   anumber NUMBER(10),
  3   avarchar2 VARCHAR2(100),
  4   adate DATE);

Table created.

SQL> SELECT table_name, compression, compress_for
  2    FROM user_tables
  3   WHERE table_name IN ('TABLE_TEST', 'TABLE_COMP_TEST');

TABLE_NAME                     COMPRESS COMPRESS_FOR                            
------------------------------ -------- ------------                            
TABLE_COMP_TEST                ENABLED  OLTP                                    
TABLE_TEST                     DISABLED              

SQL> DECLARE
  2    x number := 1000000;
  3  BEGIN
  4   WHILE x > 0 LOOP
  5    INSERT INTO table_test VALUES (x,RPAD('0',100,'0'),SYSDATE);
  6    x := x - 1;
  7   END LOOP;
  8  END;
  9  /

PL/SQL procedure successfully completed.

SQL> DECLARE
  2     x number := 1000000;
  3  BEGIN
  4   WHILE x > 0 LOOP
  5    INSERT INTO table_comp_test VALUES (x,RPAD('0',100,'0'),SYSDATE);
  6    x := x - 1;
  7   END LOOP;
  8  END;
  9  /

PL/SQL procedure successfully completed.

SQL> COMMIT;

SQL> EXEC DBMS_STATS.gather_schema_stats(USER, cascade => TRUE);

PL/SQL procedure successfully completed.

SQL> SELECT table_name, num_rows, blocks, empty_blocks
  2   FROM user_tables
  3  WHERE table_name IN ('TABLE_TEST', 'TABLE_COMP_TEST');

TABLE_NAME                       NUM_ROWS     BLOCKS EMPTY_BLOCKS               
------------------------------ ---------- ---------- ------------               
TABLE_COMP_TEST                   1000000       2008            0               
TABLE_TEST                        1000000      17237            0

-- please note difference in blocks

Example of Partition level Compression

SQL> CREATE TABLE part_comp_test(
  2    anumber NUMBER(10),
  3    avarchar2 VARCHAR2(100),
  4    adate DATE)
  5  PARTITION BY RANGE (adate) (
  6    PARTITION PART_COMP_TEST_P1 VALUES LESS THAN 
  7       (TO_DATE('01/01/2012', 'DD/MM/YYYY')) COMPRESS,
  8    PARTITION PART_COMP_TEST_P2 VALUES LESS THAN 
  9       (TO_DATE('01/01/2013', 'DD/MM/YYYY')) COMPRESS 
 10       FOR DIRECT_LOAD OPERATIONS,
 11    PARTITION PART_COMP_TEST_P3 VALUES LESS THAN 
 12       (TO_DATE('01/01/2014', 'DD/MM/YYYY')) COMPRESS FOR OLTP,
 13    PARTITION PART_COMP_TEST_P4 VALUES LESS THAN (MAXVALUE) NOCOMPRESS
 14  );

Table created.

SQL> DECLARE
  2     x number := 1000000;
  3     y date ;
  4     c number := 4;
  5  BEGIN
  6   WHILE c > 0 LOOP
  7    y := to_date('01-06-' || to_char(2010 + c), 'DD-MM-YYYY');
  8    x := 1000000;  
  9    WHILE x > 0 LOOP
 10     INSERT INTO part_comp_test VALUES (x,RPAD('0',100,'0'),y);
 11     x := x - 1;
 12    END LOOP;
 13    c := c - 1;
 14   END LOOP;
 15  END;
 16  /

PL/SQL procedure successfully completed.

SQL> COMMIT;

Commit complete.

SQL> EXEC DBMS_STATS.gather_schema_stats(USER, cascade => TRUE);

SQL> SELECT partition_name, compression, compress_for, blocks 
  2   FROM user_tab_partitions
  3  WHERE table_name = 'PART_COMP_TEST';

PARTITION_NAME                 COMPRESS COMPRESS_FOR     BLOCKS                 
------------------------------ -------- ------------ ----------                 
PART_COMP_TEST_P1              ENABLED  BASIC             15214                 
PART_COMP_TEST_P2              ENABLED  BASIC             15214                 
PART_COMP_TEST_P3              ENABLED  OLTP               2014                 
PART_COMP_TEST_P4              DISABLED                   17254

-- please note difference in blocks

How to alter compression option

SQL> ALTER TABLE TABLE_COMP_TEST NOCOMPRESS;

Table altered.

SQL> ALTER TABLE PART_COMP_TEST MODIFY PARTITION PART_COMP_TEST_P4 
COMPRESS FOR OLTP;

Table altered.

SQL> SELECT table_name, compression, compress_for
  2   FROM USER_TABLES 
  3  WHERE table_name IN ('TABLE_TEST', 'TABLE_COMP_TEST');

TABLE_NAME                     COMPRESS COMPRESS_FOR
------------------------------ -------- ------------
TABLE_COMP_TEST                DISABLED
TABLE_TEST                     DISABLED

SQL> SELECT partition_name, compression, compress_for 
  2   FROM user_tab_partitions
  3  WHERE table_name = 'PART_COMP_TEST';

PARTITION_NAME                 COMPRESS COMPRESS_FOR
------------------------------ -------- ------------
PART_COMP_TEST_P1              ENABLED  BASIC
PART_COMP_TEST_P2              ENABLED  BASIC
PART_COMP_TEST_P3              ENABLED  OLTP
PART_COMP_TEST_P4              ENABLED  OLTP

Comment