0

We are trying to create a Archival Strategy for Oracle Databases. Archiving old Records to Compressed Oracle tables has come out as a winner.

We have noticed 55% byte saving for Tables with only Text and 25% byte saving for Tables with Attachments (PDF, Word, Excel).

Please suggest

  1. If anyone has faced problems with Compressed Tables.
  2. If we have better options in Oracle to archive data. We need to refer this archived data on daily bases.

Performance is not a big concern.

We are looking for Oracle 11g Solution only.

2 Answers 2

1

As longs as you don't do ANY updates on compressed tables you are fine. The old fashioned block level compression works fine when you can add data in batches. The old block level compression only works on loads, not on regular single record inserts.

Assuming that archiving goes in batches block level compression is OK. PDF's will hardly compress using the available compressors in Oracle but when things like layout and font info is identical for many files, 7zip could be your friend but AFAIK this is not available in the database.

If you want to compress transactional data advanced compression is for you but it has a performance penalty.

When your data accesses are in scans, the compression could even give you a performance benefit because there are less io's to do. This is only true when you have plenty CPU available to do the inflight de-compression.

If you have to do updates on compressed tables, your performance is dead and your volume is back to the uncompressed volume. So be careful. You won't be the first one who is hit by this feature. This is also very true for the archive compression mode in Advanced Compression.

0

There are at least 3 types of compression on Oracle. Only one is free of charge - the "offline" one. I call it offline because it requires exclusive access to a table. This "offline" compression can be used together with partitioning. Simply partition you databa by years and the use statement:

ALTER TABLE <table_name> MOVE PARTITION <year2000> COMPRESS;

This will compress your data on block level. The bigger the block size of the tablespace the better ratio you can can expect.

For PDFs the only usable compression is COLUMNAR COMPRESSION. No other compression type can not deal with BLOBs. You can also use our own Java stored procedure to use ZIP (or 7Zip) on BLOBs.

PS: I worked on SW which was able to pull out images and fronts from PDFs. And reconstruct PDFs on demand. The achieved ratio was bigger than 90% (on common corporate documents), but this product is closed source and is dead now.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.