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

Do not use compressed table, but you can use compressed index, since
index can be rebuilded.

In oracle 9i, compress table is not so robust!

AnySQL (d.c.b.a) schrieb:
> Do not use compressed table, but you can use compressed index, since
> index can be rebuilded.
>
> In oracle 9i, compress table is not so robust!
>

Not sure, what is meant under "robust".
Tables can every time be converted to compressed/uncompressed by simple
alter table ... move compress/nocompress.
There are some limitations bounded to , but altogether table compression
is an excellent feature, if properly ( in suitable environment )
applied. Benefits are obvious - saving of storage, much faster fts, much
faster calculation of aggregates. Combined with partitioning makes it
even more flexible because different partitions can be compressed
selectively.

Best regards

Maxim

astalavista wrote:
> 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

I am aware of no limitations or issues with compressed tables
or compressed indexes in Oracle 9i though I would suggest you
move to 9.2.0.6.

The most important thing though is to understand what Oracle
means by compression. This is not .zip type compression. The
compression in tables and indexes is the removal of duplicates.
So if your information has very high cardinality you may gain
little or nothing: Test.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)

Your not aware of limit/issue with compressed table, does not stand for
compressed table is perfect.

Search metalink, bunches of bugs/limit with compressed table.
But your later words are correct. No feature is bug free. We need to
know the constraints and benefit of each feature, before we put it into
production.

"astalavista" <spam@nowhere.com> wrote in message
news:di7uhp$2hq$1@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

hey astalavista,

Oracle version 9.2 has a tiny bug in compressed tables:
Once compressed if you try to do "alter table add column"
you get a interesting error message:
ORA-22856: cannot add columns to object tables

It you try to "uncompress" it and add a column
you will get the same error. So now you're stuck
with a 10 billion records compressed table
which cannot be altered. ;-)

It's Bug 2421054 ,on metalink :
* In 9i errors are reported for ADD/DROP
but the text may be misleading, eg:
ADD column fails with "ORA-22856: cannot add columns to object tables"
DROP column fails with "ORA-12996: cannot drop system-generated virtual
column"

* Note that a table which was previously marked as compress which has
now been altered to NOCOMPRESS also signals such errors as the
underlying table could still contain COMPRESS format datablocks.

It is fixed in 10G though and - the fix is considered
an enhancement - well, well.

Cheers, Roelof

"astalavista" <spam@nowhere.com> schreef in bericht > 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

Jonathan Lewis wrote:

> 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.

This is absolutely true but I'd like to argue that as
with Clusters, and Index Organized Tables, developers
can, in many cases, ignore the overhead.

And yes this is just one of those personal points-of-view
so I want to acknowledge that up front.

<RANT>
Too often developers concentrate on getting information into
a database and ignore subsequent down-stream uses of the data.
We have quite a few projects built here in Washington State we
call black holes because they suck up all information in their
immediate neighborhood but nothing ever comes back out.

Lets say, for purposes of argument, it adds 10ms to every insert
or update, using a compressed table or other CPU intensive means
of storing the data. Every transaction will take 10ms longer. So
what?

How many times can a row be inserted? Once!
How many times can a row be deleted? Once!
How many times is a row, realistically, updated? Once? Twice?
How many times is a row queried? Over and over and over again!

Optimize for the report writers. Optimize for the people that are
hitting the same row over and over again for today's totals, the
week's totals, the month's totals, this quarter vs. last quarter,
this year vs. last year.

You will find far fewer complaints from management.
</RANT>

And of course the above rant can and should be ignored in many
situations. Still it is worth keeping in mind as rarely does anyone
review the reporting requirements until after the applications is
already designed ... or worse ... built.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)

DA Morgan wrote:
> Jonathan Lewis wrote:
>
> > 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.
>
> This is absolutely true but I'd like to argue that as
> with Clusters, and Index Organized Tables, developers
> can, in many cases, ignore the overhead.
>
> And yes this is just one of those personal points-of-view
> so I want to acknowledge that up front.
>
> <RANT>
> Too often developers concentrate on getting information into
> a database and ignore subsequent down-stream uses of the data.
> We have quite a few projects built here in Washington State we
> call black holes because they suck up all information in their
> immediate neighborhood but nothing ever comes back out.
>
> Lets say, for purposes of argument, it adds 10ms to every insert
> or update, using a compressed table or other CPU intensive means
> of storing the data. Every transaction will take 10ms longer. So
> what?
>
> How many times can a row be inserted? Once!
> How many times can a row be deleted? Once!
> How many times is a row, realistically, updated? Once? Twice?
> How many times is a row queried? Over and over and over again!
>
> Optimize for the report writers. Optimize for the people that are
> hitting the same row over and over again for today's totals, the
> week's totals, the month's totals, this quarter vs. last quarter,
> this year vs. last year.
>
> You will find far fewer complaints from management.
> </RANT>
>
> And of course the above rant can and should be ignored in many
> situations. Still it is worth keeping in mind as rarely does anyone
> review the reporting requirements until after the applications is
> already designed ... or worse ... built.
> --
> Daniel A. Morgan

I've beem using PCTFREE 0 COMPRESS as the standard setting for
bulk-populated warehouse tables and the materialized views we use for
loading files (Oracle 9.2.0.5), and it seems to work pretty well. You
can't easily add columns to MVs anyway, and these ones are not
updateable, and our queries mostly struggle with disk IO rather than
CPU, so I don't see a downside.

William Robertson wrote:
> DA Morgan wrote:
>
>>Jonathan Lewis wrote:
>>
>>
>>>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.
>>
>>This is absolutely true but I'd like to argue that as
>>with Clusters, and Index Organized Tables, developers
>>can, in many cases, ignore the overhead.
>>
>>And yes this is just one of those personal points-of-view
>>so I want to acknowledge that up front.
>>
>><RANT>
>>Too often developers concentrate on getting information into
>>a database and ignore subsequent down-stream uses of the data.
>>We have quite a few projects built here in Washington State we
>>call black holes because they suck up all information in their
>>immediate neighborhood but nothing ever comes back out.
>>
>>Lets say, for purposes of argument, it adds 10ms to every insert
>>or update, using a compressed table or other CPU intensive means
>>of storing the data. Every transaction will take 10ms longer. So
>>what?
>>
>>How many times can a row be inserted? Once!
>>How many times can a row be deleted? Once!
>>How many times is a row, realistically, updated? Once? Twice?
>>How many times is a row queried? Over and over and over again!
>>
>>Optimize for the report writers. Optimize for the people that are
>>hitting the same row over and over again for today's totals, the
>>week's totals, the month's totals, this quarter vs. last quarter,
>>this year vs. last year.
>>
>>You will find far fewer complaints from management.
>></RANT>
>>
>>And of course the above rant can and should be ignored in many
>>situations. Still it is worth keeping in mind as rarely does anyone
>>review the reporting requirements until after the applications is
>>already designed ... or worse ... built.
>>--
>>Daniel A. Morgan
>
>
> I've beem using PCTFREE 0 COMPRESS as the standard setting for
> bulk-populated warehouse tables and the materialized views we use for
> loading files (Oracle 9.2.0.5), and it seems to work pretty well. You
> can't easily add columns to MVs anyway, and these ones are not
> updateable, and our queries mostly struggle with disk IO rather than
> CPU, so I don't see a downside.

Good point. Tuning to minimize CPU when you don't have a CPU issue is
a waste of time. One should always know the resources being used and
where the weakest link is, before tuning.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)