Re: Compressed tables

"astalavista" <spam_at_nowhere.com> wrote in message news:di7uhp$2hq$1_at_apollon.grec.isp.9tel.net...
> Hi,
>
> We have a datawarehouse
> and we have some space problem.
> I thought about using compressed tables
> for rarely accessed tables ( archived data )
>
> Is it a reliable technology ?
> What are the limitations ?
> Any advice ?
>
> Thanks in advance
>
> Oracle 9.2.0.5 on AIX 5.1
>
>

The most important point to bear in mind is that table compression should really only be used on read-only tables (or partitions). It applies only on bulk-processing (e.g. 'create as select') not on ordinary updates etc, so you don't want to allow ordinary operations to take place on the data.

There may be some CPU overhead to using
compressed blocks, as rows have to be
dynamically rebuilt (in memory, that is) at query time - and this is probably a little more expensive than doing a simple row-read. As someone else pointed out, the compression is not a Zip-like compression of the table - it is a block by block mechanism that eliminates duplicates within a block creating a block-level list of repeated items, and replacing columns in rows with pointers into the table.

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Cost Based Oracle: Fundamentals
Now available to pre-order.

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 4th Sept 2005