Re: Compress for OLTP

From: Jonathan Lewis <>
Date: Fri, 28 Dec 2012 13:36:22 -0000
Message-ID: <>

"Mladen Gogala" <> wrote in message
| 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
| > 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:


Jonathan Lewis

Author: Oracle Core (Apress 2011) Received on Fri Dec 28 2012 - 14:36:22 CET

Original text of this message