Table compression in Oracle

This feature has been introduced in 9i rel 2 and is most useful in a warehouse environmen.


-- Compression can be done in a simple way


SQL> alter table dummy_table move compress;

Table altered

Oracle compress data by eliminating duplicate values within a data-block. Any repetitive occurrence of a value in a block is replaced by a symbol entry in a “symbol table” within the data block. So for example any column value is repeated 5 times within a data block, it will be only stored once and rest 4 times a symbol entry will be stored in symbol table.

Its very important to know that every data block is self contained and sufficient to rebuild the uncompressed form of data.

Table compression can significantly reduce disk and buffer cache requirements for database tables while improving query performance. Compressed tables use fewer data blocks on disk, reducing disk space requirements.

 How to Uncompress 

SQL> alter table dummy_table move nocompress;

Table altered

Identifying tables to compress:

First create the following function which will get you the extent of compression

create OR REPLACE function compression_ratio (tabname varchar2)

return number is

pct number := 0.000099;

blkcnt number := 0;

blkcntc number;

begin

     execute immediate ' create table TEMP$$FOR_TEST pctfree 0 as select * from '|| tabname||' where rownum < 1';

     while ((pct < 100) and (blkcnt < 1000))

     loop

           execute immediate 'truncate table TEMP$$FOR_TEST';

           execute immediate 'insert into TEMP$$FOR_TEST select * from '||tabname||' sample block ('||pct||',10)';

           execute immediate 'select count(distinct(dbms_rowid.rowid_block_number(rowid))) from TEMP$$FOR_TEST' into blkcnt;

           pct := pct * 10;

     end loop;

     execute immediate 'alter table TEMP$$FOR_TEST move compress';

     execute immediate 'select count(distinct(dbms_rowid.rowid_block_number(rowid))) from TEMP$$FOR_TEST' into blkcntc;

     execute immediate 'drop table TEMP$$FOR_TEST';

return (blkcnt/blkcntc);

END;

/

Example

CREATE TABLE WORKING_RAJ AS SELECT * FROM WORKING_LINES;

SQL>select bytes/1024/1024 "Size in MB" from user_segments where segment_name='WORKING_RAJ';

Size in MB

----------

        51

SQL>ALTER TABLE WORKING_RAJ MOVE COMPRESS;

Table altered.

select bytes/1024/1024 "Size in MB" from user_segments where segment_name='WORKING_RAJ';

Size in MB

----------

        30

SQL>ALTER TABLE WORKING_RAJ MOVE NOCOMPRESS;

Table altered.

SQL>select bytes/1024/1024 "Size in MB" from user_segments where segment_name='WORKING_RAJ';

Size in MB

----------

        46

• NOCOMPRESS - The table or partition is not compressed. This is the default action when no compression clause is specified.
• COMPRESS - This option is considered suitable for data warehouse systems. Compression is enabled on the table or partition during direct-path inserts only.
• COMPRESS FOR DIRECT_LOAD OPERATIONS - This option has the same affect as the simple COMPRESS keyword.
• COMPRESS FOR ALL OPERATIONS - This option is considered suitable for OLTP systems. As the name implies, this option enables compression for all operations, including regular DML statements. This option requires the COMPATIBLE initialization parameter to be set to 11.1.0 or higher. In 11gR2 this option has been renamed to COMPRESS FOR OLTP and the original name has been deprecated.