I’m working with Leslie and our goal for compression is a reduction in I/O on a heavily I/O bound system where we have spare CPU and memory. I’ve used it in other places and found a reduction in the time and I/O for a Full Table Scan on Oracle 10G.

http://www.oracle.com/technology/products/bi/db/10g/pdf/twp_data_compression_10gr2_0505.pdf

“Oracle Database 10g Release 2 Enterprise Edition provides a unique way to deal with this cost by compressing data stored in relational tables with virtually no negative impact on query time against that data, thereby enabling substantial cost savings.

Oracle Database 10g Release 2 provides a unique compression technique that is very attractive for large data warehouses…..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. It ensures that compressed data is never larger than uncompressed data…..

Cost of compression
As a result of Oracle’s unique compression technique, there is no expensive decompression operation needed to access compressed table data. This means that the decision as to when to apply compression does not need to take a possible negative impact on queries into account….

Performance impact on loads and DML
Compressing data has a performance impact on loads, DML statements, and queries. Oracle has run numerous experiments to measure the performance characteristics of compression, and this section summarizes the results.
Compression overhead is most visible at bulk load time. For example, for simple loads, compressing data may cause up to twice the CPU usage on average. If run on a system with unlimited IO bandwidth, this may translate into doubling the load time. However, bulk loads are IO-bound on many systems. In those cases, since compression reduces the amount of data to be written, there would be some benefit in terms of elapsed load time to offset the cost of additional CPU usage.

David O’Connell
Time Customer Service
Database Administration.