Compress and nocompress table in Oracle

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
);

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.