Table Compression in Oracle
Save storage and improve performance with Oracle table compression.
Oracle enterprise edition allows you to compress tables or specific partitions to save disk space. This feature was introduced in Oracle 9i version.
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