Compress and nocompress table in Oracle

Compress allow to reduce the disk space utilization for a table. It is used to save the hard disk space but it will increase the overhead during retrieve and store data. It is worked for Direct path insert operation not with insert,update, delete with OLTP systems.

Check the table compression in Oracle

SQL> SELECT table_name,compression, compress_for from user_tables where table_name = 'TEST1';

TABLE_NAME COMPRESS COMPRESS_FOR
---------- -------- -------------
TEST1      DISABLED

Enable and Disable Compression for the table

--Enable Compress for the table
ALTER TABLE test1 COMPRESS;


--Disable compress for the table
ALTER TABLE test1 NOCOMPRESS;

Note: This command doesn’t effect the existing data, only affect new data insert with direct path load.

For Compress the existing data you should move data with compress with following command

-- Compress existing data by moving
ALTER TABLE test1 MOVE COMPRESS;


--Uncompress existing data by moving:
ALTER TABLE test1 MOVE NOCOMPRESS;


Note: We can use compress in create statement also.

-- Example of table with compress clause
CREATE TABLE test2(id NUMBER(10)) COMPRESS;


Use in Partition of tables
Check the partition of table is compress or nocompress

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


Enable and disable compress in Partition of table

-- Enable
ALTER TABLE test1 MOVE PARTITION test1_q1 COMPRESS;
--Disable
ALTER TABLE test1 MOVE PARTITION test1_q1 NOCOMPRESS;


Example of Create partition DDL:

CREATE TABLE test1 (
id NUMBER(10),
created_date DATE
) NOCOMPRESS
PARTITION BY RANGE (created_date) (
PARTITION test1_q1 VALUES LESS THAN (TO_DATE('01/04/2020', 'DD/MM/YYYY')) COMPRESS
);