Conor O'Mahony's Database Diary

Your source of IBM database software news (DB2, Informix, Hadoop, & more)

Archive for August 10th, 2009

Data Compression in IBM DB2 and Oracle Database

with 6 comments

Last week, in my blog post titled Truth in Advertising – Advanced Data Compression in Oracle 11g, I discussed the fact that data compression rates are highly dependent upon 1) the nature of the data and 2) the database environment. I’d like to follow up on that post with a data point that directly compares IBM DB2 and Oracle Database.

Last week, I mentioned that TPC-C data contains random strings. As such, it is not an ideal data set for measuring data compression rates. TPC-H is actually a better data set, as it contains data that is closer in nature to “real world” data. There are issues with TPC-H data as well. Since the data is programmatically generated, most columns have a uniform distribution, which limits the clustering of data that tends to happen in “real world” data sets. Also, there are some “fill columns” with long unique content that is difficult to compress. Nonetheless, the TPC-H data is a better data set for comparing data compression rates than TPC-C.

Luckily for us, Oracle have published their compression results for TPC-H data in a paper at the Conference on Very Large Data Bases (VLDB) titled Data Compression in Oracle. In this paper, they write that:

When loaded with the compression feature, compression for LINEITEM is the highest at a compression factor of about 1.6, while ORDER compresses at a compression factor of about 1.2… LINEITEM shrinks from 79 GB to 49 GB while ORDERS shrinks from 17 GB to 14 GB.

When we compare these rates to the compression rates that DB2 gets for TPC-H data, we see that DB2 has significantly higher compression rates. And, of course, higher compression rates are better because they mean you need less storage. Nobody needs to be reminded that storage-related costs are often the most expensive component of a system. Lowering storage costs can have a very real impact on IT budgets.

TPC-H Table Oracle DB2
LINEITEM 38% 58%
ORDERS 18% 60%

Please keep in mind that these results are only for the TPC-H data, and your data may achieve different compression rates. For instance, Tellabs and SunTrust Bank both report using DB2 to reduce the size of database tables by as much as 83%. The only way to know what kind of compression rates you will experience is to get the database vendors to run their tools on your data and let you know.


Written by Conor O'Mahony

August 10, 2009 at 11:27 am

Upcoming DB2 Regional Events

leave a comment »

The Independent DB2 User Group (IDUG) is hosting a number of regional events in November. These two-day events bring the latest DB2 sessions and the leading DB2 speakers to the following areas:

  • Camp Hill, PA on 3-4 Nov
  • Dallas, TX on 9-10 Nov
  • Austin, TX on 12-13 Nov
  • Minneapolis, MN on 16-17 Nov
  • Kansas City, MO on 18-19 Nov

I expect details for the events to be posted soon on the IDUG events calendar.

IDUG Regional Events 2009

Written by Conor O'Mahony

August 10, 2009 at 10:26 am

%d bloggers like this: