• Arun Kumar

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.


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

503 views

Recent Posts

See All

Oracle 12c to 19c Database Upgrade

In this article we will be looking at two most commonly used methods of upgrading Oracle database from 12c to 19c Pre-upgrade tasks Upgrade using DBUA Upgrade using Manual Method You can perform a dir