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.