Re: Compress for OLTP
Date: Fri, 28 Dec 2012 13:36:22 -0000
Message-ID: <LKednV_dk9rIPUDNnZ2dnUVZ8kadnZ2d_at_bt.com>
"Mladen Gogala" <gogala.mladen_at_gmail.com> wrote in message news: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.
|
That should have been deduplication WITHIN each block.
Oracle simply collects a list of duplicated values (and the same value could be reaped from several columns) and builds a "table of duplicates/tokens" in the block, then replaces values in the base rows with "pointers" to the token table. Basic compression is effectively read-only and dependent on direct path loading - and sets pctfree to 0. OLTP simply allows DML to use up the free space in the block, and then (subject to some unfortunate limitations) rebuilds a block as a compressed block when it runs out of free space (which releases some space, so that more rows can be inserted/updated until the block is full again).
The articles will only be on the Redgate site, but I'll be linking to them.
In the meantime, Randolf Geist has a couple of very interesting items about
OLTP compression (and its failings) on his blog - most significantly for
the Oracle-L discussion, perhaps:
http://oracle-randolf.blogspot.co.uk/2011/05/assm-bug-reprise-part-2.html
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com/all-postings
Author: Oracle Core (Apress 2011)
http://www.apress.com/9781430239543
Received on Fri Dec 28 2012 - 14:36:22 CET