• 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

Recent Posts

See All

How to plan database creation

Database creation process involves several operating system files to work together. You create database only once and later on keep on adding more data files. Before you can simply go ahead and start

Oracle Transportable Tablespace

Assume that you have a schema (IQS) on production server and you want to import the schema into test server. The ideal approach would be to perform entire schema export and import using data pump. Rig

Grant Select on all tables in a schema

I encountered this situation where I wanted to grant SELECT on all the tables owned by one user to another user. There are two simple ways to achieve this: Generate SQL for each table In this method,

  • Facebook
  • Twitter
  • YouTube
  • Instagram

© 2020 by DBA Genesis

Feedback