Oracle 11G : OLTP Table Compression : Advanced Compression Option

Oracle 11G introduces a new option to support compression of various structured, unstructured and other backup data. This option named Oracle Advanced Compression option provides multiple features including OLTP table compression ( this feature extends the  basic table compression that was available since 9i ) , Secure File Deduplication, RMAN Compression and Data Pump Compression. Oracle will continue to provide the basic Table compression in the Enterprise Edition of its product, but customers will have to license the new features along with Oracle 11G to get OLTP Table Compression and others.

Oracle 11G OLTP Table Compression 

Introduced in  Oracle 9i, Table compression quickly became a favourite feature of the database administrators.  It was particularly useful in Data Warehouse environments where bulk loads were used in abundance and the compression feature allowed reduction in overall storage requirements.

In Oracle 11g, OLTP Table compression is introduced and this feature allows data to be compressed during all DML operations. Oracle 11g claims to use a unique algorithm that eliminates duplicate values within a single block even across multiple columns. Similar to Oracle 9i, each block contains a symbol table that maintains compression metadata and this allows better performance compared to the global symbol table based algorithms.

Wouldnt using a compression algorithm during OLTP introduce unnecessary overhead ?. I would guess so, but Oracle 11g claims that their threshold based compression, which only compresses the data within a block once a threshold is reached, eliminates the overhead to compress the data everytime its written. So, if the threshold was 35%, the contents of the block are compressed the first time the block reaches the 35% utilized state, again when it reaches the 70% threshold and once when its full to make sure that the maximum compression is achieved.

This process completes until the Oracle 11g database determines that no further benefits can be achieved through compression.   Lets see how well it lives us to the promises made by oracle around this feature.