Re: Compression and on-disk sorting

Andrew Piskorski wrote:
>>> Rod Taylor wrote:
>>>>Disk storage is cheap. Disk bandwidth or throughput is very
expensive.
>
> Oracle has included "table compression" since 9iR2. They report table
> size reductions of 2x to 4x as typical, with proportional reductions
> in I/O, and supposedly, usually low to negligible overhead for writes:

[...]

> The main tricks seem to be: One, EXTREMELY lightweight compression
> schemes - basically table lookups designed to be as cpu friendly as
> posible. Two, keep the data compressed in RAM as well so that you can
> also cache more of the data, and indeed keep it the compressed until
> as late in the CPU processing pipeline as possible.

Oracle's compression seems to work as follows:
- At the beginning of each data block, there is a 'lookup table'
containing frequently used values in table entries (of that block).
- This lookup table is referenced from within the block.

There is a White Paper that describes the algorithm and contains
praise for the effects:
http://www.oracle.com/technology/products/bi/pdf/o9ir2_compression_perfo
rmance_twp.pdf

Oracle does not compress tables by default.
This is what they have to say about it:

Table compression should be used with highly redundant data, such as
tables
with many foreign keys. You should avoid compressing tables with much
update
or other DML activity. Although compressed tables or partitions are
updatable,
there is some overhead in updating these tables, and high update
activity
may work against compression by causing some space to be wasted.

Yours,
Laurenz Albe