Re: Compress for OLTP

From: Mladen Gogala <>
Date: Fri, 28 Dec 2012 13:04:58 +0000 (UTC)
Message-ID: <>

On Fri, 28 Dec 2012 12:24:49 +0000, Jonathan Lewis wrote:

> Coincidentally I'm writing a short series of articles on table
> compression for Redgate, so I was tempted to point out on that thread
> that the reported effects were exactly the sort of thing you'd expect to
> see.

I am looking forward to reading the articles. Will you have them on your blog?

> Oracle doesn't do "compression" for OLTP or BASIC, by the way, it's
> simply de-duplication with a block.

De-duplication with a block or within the block? There is a different result with basic and OLTP compression, so there must be a difference. De-duplication would explain why did my table behave so poorly: I used DBMS_RANDOM to generate the value, which has made every value unique. I will try with a copy of DBA_OBJECTS, loaded several times, that should produce some duplicates.

> This has all sorts of interesting
> little side effects, some of which are not what you might expect.

I would love to learn about the algorithm. My guess is that there is a deduplication  part somewhere near every extent header into which SHA-2 numbers, calculated off the values are stored. Another possibility is an index-like structure being the part of the table. Thanks for the tip, I'll try figuring that out.

Also, LOB compression is different. I got approximately the same sizes as with the normal zip command.

Mladen Gogala
The Oracle Whisperer
Received on Fri Dec 28 2012 - 14:04:58 CET

Original text of this message