In addition to storing more data on disk, Advanced Compression can improve I/O performance. Because more data is being squeezed into less space, it's easier for the database to satisfy queries. The database is physically pulling less data from disk for the same information, so it can do it faster. The catch is that these benefits only extend to table scans, not indexed queries.

The Bottom Line Oracle Database 11g Oracle, oracle.com Very Good  8.6 criteria score weight Manageability 9 25% Performance 8 25% Availability 9 20% Scalability 8 20% Value 9 10% Cost: Editions: Standard Edition One, $149 per user license (minimum of five users) and $4,995 per processor (maximum two sockets per server); Standard Edition, $300 per user license (minimum of five users) and $15,000 per CPU (maximum four sockets per server); Enterprise Edition, $800 per user license (minimum of 25 users) and $40,000 per CPU. Enterprise Edition options: Oracle Real Application Testing, $10,000 per processor or $200 per named user; Oracle Advanced Compression, $10,000 per processor or $200 per named user; Oracle Total Recall, $5,000 per processor or $100 per named user; Oracle Active Data Guard, $5,000 per processor or $100 per named user. Platforms: Linux x86, Linux Itanium, Sun Solaris, IBM AIX5L, HP-UX, Windows NT 4 or later Bottom Line: Oracle Database 11g will be welcome relief to DBAs responsible for testing changes against a production workload. The way that Data Guard and Real Application Testing work together is sheer poetry, and no doubt most shops will quickly come to rely on the combination. The automatic health checks will help DBAs sleep better, though they may initially be leery of the SQL tuning feature. Result Cache will be a big hit with advanced users, and Advanced Compression (data de-duplication) will reward the smart DBA who manages it carefully. About our Reviews and Scoring Methodology

So Advanced Compression will improve the performance of some queries, but not others. And it will dramatically reduce storage requirements for some kinds of data, but not others. Further, even for the same kinds of data, overall compression ratios will differ depending on how often the data repeats and how you order (index) the data.

Finally, percent free (PCTFREE), the percentage of a page left empty for inserts or updates, will also be a factor. The better the data "compresses," the more of it will fit on a page, and the fewer pages the engine must traverse to satisfy the query. These gains could be seen everywhere from slightly faster queries to reduced blocking.

I tested Advanced Compression against two separate databases. In the first case, the database was generated from the TPC-C order entry benchmark, which simulates a complete environment for online transaction processing. In the second case, the database was the OLTP Table Compression Test Kit provided by Oracle.

You would expect from Advanced Compression that different tables and different data will behave differently, and this is just what my two tests showed. Two tables in the Oracle Test Kit not only compressed to a quarter of their original size, but reaped big gains in query performance and even improvements in write speeds. The TCP-C database tables compressed far less, ranging from 15 to 57 percent savings, and saw no benefit to query or write performance. In fact, performance took a hit. (See details of the two tests and tables of the results.)

Naturally, the results I got in my tests, and the results that Oracle gets in its tests, will not be the same as the results you get in your tests. Expect your compression results to differ from one table to another as well, sometimes dramatically. And be aware that making changes to the base-level table can change your compression ratio. If business or reporting requirements change (as they often do) and you have to order your table differently, you could lose a good deal of your compression. Worst-case scenario, you might suddenly find that you no longer have enough disk space.

Also note that Advanced Compression could impose a cost in system resources. When I set up the read tests for the TCP-C data, I ordered the tables to cluster all the like data together and then queried from that data specifically. I expected that clustering like data would greatly improve scan performance. But that's not what happened. So unless you test thoroughly and with your real production data, you may be surprised by the results.

Advanced Compression can be a huge asset for the smart DBA who manages it correctly and has the forethought to pull it off and maintain it well, but it adds a level of complexity to making changes that you have to be careful about. And even before you implement it, you'll need to do plenty of regression testing to ensure that your apps will work as before.