Re: Compress for OLTP
Date: Fri, 28 Dec 2012 13:04:58 +0000 (UTC)
Message-ID: <pan.2012.12.28.13.04.50_at_gmail.com>
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 http://mgogala.byethost5.comReceived on Fri Dec 28 2012 - 14:04:58 CET