Page

5

The Technology

10g Compression

A new verb COMPRESS has been added to the CREATE TABLE & CREATE INDEX statements. This

allows these objects to contain compressed data. Each row of a table basically takes up less

space. As very wide tables contain a lot of character columns, there is scope for a large amount

of redundant space to be removed. There is a CPU overhead as a row is INSERTED, as it has to

pass through the compress algorithm. Likewise, when a row is updated it has to be decom

pressed and then compressed again. There is, however, a significant benefit whereby, when a

block of data is retrieved, the number of rows in the block increases, reducing the number of

blocks that need to be retrieved. This is where the performance benefit comes from.

There is significant detail on how this works in a paper produced by Oracle:

http://www.vldb.org/conf/2003/papers/S28P01.pdf

The problem with 10g compression is that it cannot be applied to an existing ta

ble. For practical purposes, a table is either compressed or it is not. It is possible

to compress a table in situ but the table has to be locked for the period or a PL/

SQL package has to be run. The problem is not quite so bad for indexes, as these

are rebuilt from time to time and can be rebuilt with the compress option en

abled.

Compression occurs when data is being inserted, updated, bulk inserted, or bulk

loaded into a compressed table. These operations include:

Direct Path SQL *Loader

CREATE TABLE and AS SELECT statements

Parallel INSERT (or serial INSERT with an APPEND hint) statements

Single row or array inserts

Single row or array updates

A strategy which was used effectively during the test was Parallel inserts. A new table was cre

ated with no indexes and was populated using parallel inserts from the existing table. The exist

ing table was dropped, the new table renamed to the old and the indexes rebuilt.

The tests run by Grid Tools simulated tables and indexes used in a standard transaction system

and were run on version 10.2.0.1.0 of Oracle.

 

 

www.grid tools.com UK: +44 (0) 871 309 8293 US: +1 866 563 3120

 

...The problem

with 10g compres

sion is that it

cannot be applied

to an existing

table, for practical

purposes a table is

either compressed

or it is not...  

Database Shrinking and Compression Strategy