Amazing Oracle data compression benefits

 

In an outstanding Oracle paper titled “data compression in Oracle, Oracle employees Poess and Potapov deliver one of the most comprehensive tests of Oracle OLAP data compression using industry recognized TPC test case techniques. 

 

Also see my notes on the new 11g table compression.

 

This Oracle whitepaper describes some very important details on using Oracle data compression for very large Oracle databases:

 

http://www.vldb.org/conf/2003/papers/S28P01.pdf

 

Among the findings is the important suggestion that using Oracle table compression may actually improve the performance of your Oracle database:

 

“The reduction of disk space using Oracle table compression can be significantly higher than standard compression algorithms, because it is optimized for relational data.

 

It has virtually no negative impact on the performance of queries against compressed data; in fact, it may have a significant positive impact on queries accessing large amounts of data, as well as on data management operations like backup and recovery.”

 

This compression paper also suggestion that using a large blocksize may benefit Oracle databases where rows contain common redundant values:

 

“Table compression can significantly reduce disk and buffer cache requirements for database tables. Since the compression algorithm utilizes data redundancy to compress data at a block level, the higher the data redundancy is within one block, the larger the benefits of compression are.”

 

The article also cites evidence that Oracle table compression can reduce the time required to perform large-table full-able scans by half:

 

“The fts of the non-compressed table takes about 12s while the fts of the compressed table takes only about 6s.”