8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 9i » Here

Compressed Tables

Oracle 9i onward allows whole tables or individual table partitions to be compressed to reduce disk space requirements. The basic compression described here is a free feature in the Enterprise Edition of the database. It is designed for compressing static data, since it only works for direct path inserts, not single row insert, update and delete operations typical in OLTP systems.

Related articles.

Create Table

Table compression is enabled by adding the COMPRESS keyword to the end of the table definition, as shown below.

DROP TABLE test_tab;

CREATE TABLE test_tab (
  id            NUMBER(10)    NOT NULL,
  description   VARCHAR2(100) NOT NULL,
  created_date  DATE          NOT NULL,
  created_by    VARCHAR2(50)  NOT NULL,
  updated_date  DATE,
  updated_by    VARCHAR2(50)
)
COMPRESS;

The default compression status is NOCOMPRESS.

The compression status of the table can be displayed using COMPRESSION column in the [DBA|ALL|USER]_TABLES views. The introduction of alternative modes of compression in Oracle 11g (described here) came with an additional column called COMPRESS_FOR to indicate the type of compression. If you are using a version prior to 11g, reference to the COMPRESS_FOR column will cause the queries in this article to fail, so just remove them.

SELECT compression, compress_for
FROM   user_tables
WHERE  table_name = 'TEST_TAB';

COMPRESS COMPRESS_FOR
-------- ------------------------------
ENABLED  BASIC

SQL>

Alter Table

The compression status of an existing table can be changed using the ALTER TABLE statement.

ALTER TABLE test_tab NOCOMPRESS;

SELECT compression, compress_for
FROM   user_tables
WHERE  table_name = 'TEST_TAB';

COMPRESS COMPRESS_FOR
-------- ------------------------------
DISABLED

SQL>


ALTER TABLE test_tab COMPRESS;

SELECT compression, compress_for
FROM   user_tables
WHERE  table_name = 'TEST_TAB';


COMPRESS COMPRESS_FOR
-------- ------------------------------
ENABLED  BASIC

SQL>

This doesn't affect the compression of existing data, but will affect the compression of new data that is loaded by direct path loads. If you want to compress existing data, you must perform a move operation, so the data gets compressed during the copy.

ALTER TABLE test_tab MOVE NOCOMPRESS;
ALTER TABLE test_tab MOVE COMPRESS;

Remember, when you perform a move operation you will have two copies of the table for a period of time. Make sure you have enough storage to cope with this.

A similar action could be performed for an individual partition of a partitioned table.

ALTER TABLE test_tab MOVE PARTITION test_tab_q2 COMPRESS;

Partitioned Tables

Using the COMPRESS keyword at the table level makes this the default for all partitions.

DROP TABLE test_tab;

CREATE TABLE test_tab (
  id            NUMBER(10)    NOT NULL,
  description   VARCHAR2(100) NOT NULL,
  created_date  DATE          NOT NULL,
  created_by    VARCHAR2(50)  NOT NULL,
  updated_date  DATE,
  updated_by    VARCHAR2(50)
)
COMPRESS
PARTITION BY RANGE (created_date) (
  PARTITION test_tab_q1 VALUES LESS THAN (TO_DATE('01/04/2003', 'DD/MM/YYYY')),
  PARTITION test_tab_q2 VALUES LESS THAN (MAXVALUE)
);

The [DBA|ALL|USER]_TAB_PARTITIONS views display the compression status of the individual partitions.

COLUMN partition_name FORMAT A30

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

PARTITION_NAME                 COMPRESS COMPRESS_FOR
------------------------------ -------- ------------------------------
TEST_TAB_Q1                    ENABLED  BASIC
TEST_TAB_Q2                    ENABLED  BASIC

SQL>

It is possible to control the compression status of individual partitions in a partitioned table. The compression status can be specified for all partitions explicitly, or a table default can be used, with only exceptions to this default specified at partition level. In the following example the default NOCOMPRESS option is set explicitly at table level (this is not necessary), with the first partition using the COMPRESS keyword to override the default.

DROP TABLE test_tab;

CREATE TABLE test_tab (
  id            NUMBER(10)    NOT NULL,
  description   VARCHAR2(100) NOT NULL,
  created_date  DATE          NOT NULL,
  created_by    VARCHAR2(50)  NOT NULL,
  updated_date  DATE,
  updated_by    VARCHAR2(50)
)
NOCOMPRESS
PARTITION BY RANGE (created_date) (
  PARTITION test_tab_q1 VALUES LESS THAN (TO_DATE('01/04/2003', 'DD/MM/YYYY')) COMPRESS,
  PARTITION test_tab_q2 VALUES LESS THAN (MAXVALUE)
);


COLUMN partition_name FORMAT A30

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

PARTITION_NAME                 COMPRESS COMPRESS_FOR
------------------------------ -------- ------------------------------
TEST_TAB_Q1                    ENABLED  BASIC
TEST_TAB_Q2                    DISABLED

SQL>

As mentioned previously, we can alter the compression status of the partition and compress its contents using the ALTER TABLE command.

ALTER TABLE test_tab MOVE PARTITION test_tab_q2 COMPRESS;

COLUMN partition_name FORMAT A30

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

PARTITION_NAME                 COMPRESS COMPRESS_FOR
------------------------------ -------- ------------------------------
TEST_TAB_Q1                    ENABLED  BASIC
TEST_TAB_Q2                    ENABLED  BASIC

SQL>

Check the Level of Compression

Create the following table with one compressed partition and one non-compressed partititon.

DROP TABLE test_tab;

CREATE TABLE test_tab (
  id            NUMBER(10)    NOT NULL,
  description   VARCHAR2(100) NOT NULL,
  created_date  DATE          NOT NULL,
  created_by    VARCHAR2(50)  NOT NULL,
  updated_date  DATE,
  updated_by    VARCHAR2(50)
)
NOCOMPRESS
PARTITION BY RANGE (created_date) (
  PARTITION test_tab_q1 VALUES LESS THAN (TO_DATE('01/04/2003', 'DD/MM/YYYY')) COMPRESS,
  PARTITION test_tab_q2 VALUES LESS THAN (MAXVALUE)
);

Insert some rows into each partition using a direct path insert. We will do this separately and capture the elapsed time and CPU time used by each operations.

-- TEST_TAB : Direct path insert into non-compressed partition.
SET SERVEROUTPUT ON
DECLARE
  v_date        test_tab.created_date%TYPE := SYSDATE;
  v_user        test_tab.created_by%TYPE   := USER;
  l_start_time  NUMBER;
  l_start_cpu   NUMBER;
BEGIN

  l_start_time := DBMS_UTILITY.get_time;
  l_start_cpu  := DBMS_UTILITY.get_cpu_time;

  INSERT /*+ APPEND */ INTO test_tab (id, description, created_date, created_by)
  SELECT level,
         'A very repetitive, and therefore very compressible column value',
         v_date,
         v_user
  FROM   dual
  CONNECT BY level <= 1000000;
  COMMIT;

  DBMS_OUTPUT.put_line('CPU Time (hsecs)    : ' || (DBMS_UTILITY.get_cpu_time - l_start_cpu));
  DBMS_OUTPUT.put_line('Elapsed Time (hsecs): ' || (DBMS_UTILITY.get_time - l_start_time));
END;
/
CPU Time (hsecs)    : 89
Elapsed Time (hsecs): 116

PL/SQL procedure successfully completed.

SQL>


-- TEST_TAB : Direct path insert into compressed partition.
SET SERVEROUTPUT ON
DECLARE
  v_date        test_tab.created_date%TYPE := TO_DATE('31/03/2003', 'DD/MM/YYYY');
  v_user        test_tab.created_by%TYPE   := USER;
  l_start_time  NUMBER;
  l_start_cpu   NUMBER;
BEGIN

  l_start_time := DBMS_UTILITY.get_time;
  l_start_cpu  := DBMS_UTILITY.get_cpu_time;

  INSERT /*+ APPEND */ INTO test_tab (id, description, created_date, created_by)
  SELECT level,
         'A very repetitive, and therefore very compressible column value',
         v_date,
         v_user
  FROM   dual
  CONNECT BY level <= 1000000;
  COMMIT;

  DBMS_OUTPUT.put_line('CPU Time (hsecs)    : ' || (DBMS_UTILITY.get_cpu_time - l_start_cpu));
  DBMS_OUTPUT.put_line('Elapsed Time (hsecs): ' || (DBMS_UTILITY.get_time - l_start_time));
END;
/
CPU Time (hsecs)    : 137
Elapsed Time (hsecs): 139

PL/SQL procedure successfully completed.

SQL>

From repeated runs we see direct path inserts into into a compressed table or partition requires more CPU and takes longer compared to an uncompressed table or partition.

Once the statistics are gathered we can check the compression using the [DBA|ALL|USER]_TAB_PARTITIONS views.

EXEC DBMS_STATS.gather_schema_stats(USER, cascade => TRUE);

COLUMN table_name FORMAT A20
COLUMN partition_name FORMAT A20

SELECT table_name,
       partition_name,
       compression,
       num_rows,
       blocks,
       empty_blocks
FROM   user_tab_partitions
WHERE  table_name = 'TEST_TAB'
ORDER BY 1;

TABLE_NAME           PARTITION_NAME       COMPRESS   NUM_ROWS     BLOCKS EMPTY_BLOCKS
-------------------- -------------------- -------- ---------- ---------- ------------
TEST_TAB             TEST_TAB_Q1          ENABLED     1000000       1421            0
TEST_TAB             TEST_TAB_Q2          DISABLED    1000000      12483            0

SQL>

What we see here is the compressed partition is almost an order of magnitude smaller for the same number of rows based on the number of blocks used to hold the data, so if storage size is an issue, the overhead in CPU and elapsed time for direct path inserts may be worth it.

Remember, the level of compression will vary depending on the data. In this case we cheated and used the same text data, username and created date for each row, making the data highly compressible in the block. A different data set may not have produced such a dramatic result.

Considerations

Some things to consider before using this functionality.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.