Table Compression in Oracle

Oracle enterprise edition allows you to compress tables or specific partitions to save disk space. This feature was introduced in Oracle 9i version.

• Create compressed table

• Check compression status

• Change compression status

• Compress partitions

• Check partition compression

• Change partition compress type

• See space savings with compress

Note: Do not use this option unnecessarily. You must read the considerations at the end of this article

Create compressed table

You must add COMPRESS clause while creating a table. By default, every table is created with NOCOMPRESS option

CREATE TABLE COMP_TEST(
  id number(4), 
  std_name varchar2(20)
)
COMPRESS;

Check compression status

You can query DBA_TABLES to check compression status of a table

SELECT TABLE_NAME, COMPRESSION FROM DBA_TABLES
WHERE TABLE_NAME LIKE 'COMP_TEST';

Change compression status

You can always change compression status of an existing table.

ALTER TABLE COMP_TEST NOCOMPRESS;
ALTER TABLE COMP_TEST COMPRESS;

Note: if you are enabling compression for an existing table, this will not affect the existing data inside table. Only the new data inserted will be compressed.

If you want to compress existing data inside the table, then you must perform move operation

ALTER TABLE COMP_TEST MOVE NOCOMPRESS;
ALTER TABLE COMP_TEST MOVE COMPRESS;

Compress partitions

When you use COMPRESS clause while creating a table with partitions, then all the partitions will be by default be COMPRESSED

CREATE TABLE COMP_PART_TEST(
  ID NUMBER(4),
  STD_NAME VARCHAR2(20),
  JOIN_DATE DATE NOT NULL,
)
COMPRESS
PARTITION BY RANGE (join_date) (
  PARTITION comp_part_1 VALUES LESS THAN (TO_DATE('01/01/2016','DD/MM/YYYY')),
  PARTITION comp_part_2 VALUES LESS THAN (MAXVALUE)
);

You can compress even individual partitions too

CREATE TABLE COMP_PART_TEST(
  ID NUMBER(4),
  STD_NAME VARCHAR2(20),
  JOIN_DATE DATE NOT NULL,
)
NOCOMPRESS
PARTITION BY RANGE (join_date) (
  PARTITION comp_part_1 VALUES LESS THAN (TO_DATE('01/01/2016','DD/MM/YYYY')) COMPRESS,
  PARTITION comp_part_2 VALUES LESS THAN (MAXVALUE)
);  

Check partition compression

You can use below query to check each partition compression status

SELECT partition_name, compression
FROM   user_tab_partitions
WHERE  table_name = 'COMP_PART_TEST'
ORDER BY 1;

Change partition compress type

As we can change compression status for an existing table, we can even change compression status for an existing partition

ALTER TABLE comp_part_test MOVE PARTITION comp_part_2 COMPRESS;

See space savings with compress

Now that you have used compress option for a table, you must be able to see the block savings. First gather statistics

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

Run below command to know exact block usage. Make sure to run below command before you compress a table so that you are able to see the difference in BLOCKS column

SELECT table_name,
       partition_name,
       compression,
       num_rows,
       blocks,
       empty_blocks
FROM   user_tab_partitions
WHERE  table_name = 'COMP_TEST'
ORDER BY 1;

Considerations

Make sure you keep in mind below considerations before going for table level compression

• Compression is not applied to LOB columns

• Compression does speed up data reads

• There is overhead on CPU for data loading

• The default (basic compression) is used only for direct path load

• To enable compression for all operations, use COMPRESS FOR ALL OPERATIONS clause

• Do not use basic compression for OLTP operations