After successfully converting dozens of tables in an Oracle data warehouse from uncompressed to compressed without so much as a single glitch, several problems cropped up last week.

We are also using BusinessObjects (now SAP) Data Integrator for ELT operations and have been for over 2 1/2 years also without much issue generally speaking.  The tables in question were just recently converted to compressed and then we started seeing a relatively small number of problems with our Data Integrator loads.

The problem presented itself when we truncated the compressed tables and then subsequently reloaded them.  We then seemed to be having problems with duplicated data.  No errors were given with truncating the tables in question.  It wouldn't seem possible that we'd get a problem with duplication after truncating a table, but nothing is taken for granted.  There was apparently no duplication in the source data used to popuate these tables.

When we switched the tables involved that we had changed back to the original attribute of uncompressed, the problem immediately went away.  And though we have converted dozens of tables to use compression as mentioned, all of which part of Data Integrator load jobs, we've only seen these issues involving a few converted tables.

We still have more investigation to do on this and we're not entirely certain it's related to the use of compression, but it sure seems more than just coincidence.  Also I don't know if I have the facts stated 100% correct but I think my details are pretty accurate nonetheless.  We'll see what we can determine but I'm still satisfied the use of compressed tables is reliable and worth the space we have reclaimed.

An on-going task as part of an Oracle based data warehouse with which I've helped develop is to review and enhance the design of schema objects (tables and indexes for example).  Space utilization and performance are among the primary characteristics to review.  You may suggest, possibly even strongly, that these are factors to be included in the initial design stages.

To that I can respond that they were.  At least to the best knowledge available to the development team at the time of initial design.  The good news is that the data warehouse and the reporting and analysis activities it makes possible has continue to increase both in data available and in use and value to the business overall.  While growth is the majority trend, some data has not been used or does not change as much as initially estimated.

One of things we've been doing is implementing the compression feature of Oracle on tables and indexes were appropriate.  With many months of historical data now available from a substantial and growing list of source systems, we are going back to look for data with a high occurrence of repeated values as candidates for compression.  It turns out this is a majority of the data.  As implemented thus far over several months,  compression has reclaimed upwards of 50% of previously used space without compression.

And the performance hit as been unnoticeable.  In fact daily ELT process may have increased as increased I/O efficiency outweighs slightly increased CPU overhead for compression processing during DML.  The nice thing about this experience with implementing Oracle compression is that it is not a theoretical exercise.  This is our actual experience and we track before and after metrics to observe any positive or negative effects. 

After four or more months of applying compression, the results are only positive.