Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Compressed tables

Re: Compressed tables

From: William Robertson <william.robertson_at_bigfoot.com>
Date: 9 Oct 2005 14:51:05 -0700
Message-ID: <1128894665.862003.180060@o13g2000cwo.googlegroups.com>


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. Received on Sun Oct 09 2005 - 16:51:05 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US