Most Recently Added Layouts
In this blog, I have a series of short posts comparing data compression in DB2 and Oracle Database:
- Even More Reasons Why DB2 Data Compression is Better than Oracle Database
- Why DB2 Data Compression is Better than Oracle Database
- Data Compression in IBM DB2 and Oracle Database
I am overdue writing a brief update to cover the data compression features added in DB2 10. As you can see from the earlier blog posts, DB2 compares favorably with Oracle Database when it comes to data compression. DB2 compresses temp space, whereas Oracle Database does not. DB2 offers superior index compression. And DB2′s table-wide dictionaries often out-compress Oracle’s page-level dictionaries.
DB2 10 adds further differentiation. It adds compression for archive logs. But, more significantly, DB2 10 adds a new approach for table compression. IBM is calling this new approach “adaptive compression”. Essentially what IBM are doing is adding page-level dictionaries on top of their existing table-wide dictionaries. Now DB2 can find both globally recurring patterns and locally recurring patterns, delivering even greater compression ratios. Here is what Coca Cola Bottling have seen:
Our original migration from Oracle Database to IBM DB2 brought us a 40% storage savings. Upgrading to DB2 9.7 and utilizing index compression saved us another 17% in storage, bringing our average savings to 57%. Now, using the adaptive compression feature of DB2 10, we’ve seen an additional 20% savings, bringing our average compression savings for our databases to 77%, a dramatic storage savings!
Of course, the compression ratios that you can expect to see depend on your data and your environment. And those compression rates will vary greatly from user to user. That being said, our users have reported seeing some impressive compression ratios. Quite a few users have seen tables compressed by 10x or more. One user even saw a table compressed by 45x, but that was an extreme case that will not be representative of what you can expect. I’m hesitant to provide guidance on what you can expect because ratios do vary so greatly, but what I will say is that I have seen quite a few instances of 7x-8x overall compression for database tables.