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: DA Morgan <damorgan_at_psoug.org>
Date: Sun, 09 Oct 2005 15:23:44 -0700
Message-ID: <1128896622.810276@yasure>


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_at_x.washington.edu
(replace x with u to respond)
Received on Sun Oct 09 2005 - 17:23:44 CDT

Original text of this message

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