Introduction of Oracle Table Compression since Oracle 9i has allowed the compression of Tables, Partitions and Materialized views. This feature targeted at OLAP and Warehousing applications allows for drastic reduction in disk space and memory requirements when used appropriately.

How does Oracle Table Compression Work ?

Oracle Table Compression works on Heap Organized tables and works through the elimination of duplicate values in a block. The compression algorithm creates an entry in the symbol table for a particular column based on the occurences of the data and the column width. All subsequent values are replaced by the symbol to achieve a compression in the table. Oracle Compression algorithm makes sure that the all the data needed to recreate the uncompressed data is available within the same block.

Database Performance Impact

When compressed blocks are loaded by the server processes into Database Buffer Cache, the blocks stay compressed and this allows for the reduction in the overall memory requirement as well as an increase in the probability of a buffer cache hit

Compressed tables can cause processing overhead for DML Operations and are best suited for read-only or transactional environments with rolling hot spots. This option will certainly improve your performance for many read operations when your system is I/O bound.

Estimating Compression Factor

Table compression works by eliminating column value repetitions within individual blocks. Duplicate values in all the rows and columns in a block are stored once at the beginning of the block, in what is called a symbol table for that block. All occurrences of such values are replaced with a short reference to the symbol table. The compression is higher in blocks that have more repeated values.

Before compressing large tables you should estimate the expected compression factor. The compression factor is defined as the number of blocks necessary to store the information in an uncompressed form divided by the number of blocks necessary for a compressed storage.

Tuning to Achieve a Better Compression Ratio

Oracle achieves a good compression factor in many cases with no special tuning. As a database administrator or application developer, you can try to tune the compression factor by reorganizing the records when the compression actually takes place. Tuning can improve the compression factor slightly in some cases and very substantially in other cases.