Re: performance question: sum values vs. store calculations

Hi.

 The major advantage of materialized view is automatic  query re-write by CBO:
 Nobody have to remember what table stores totals and to  write selects from that table ( you may be surprized how  much people may forget in just a couple of years after  the project went to prod ).

  Michael.

In article <zh2z3.2449$E46.3084_at_news.rdc1.pa.home.com>,   "Bob Fazio" <bob_fazio_at_hotmail.com.no.spam> wrote:
> Oracle's 8i (materialized views) are just a hair more than a snapshot
in any
> other version of oracle. If you have the luxury of considering a
table of
> sums, you should consider a snapshot.
>
> Snapshots are views that store the results in a tablespace (just like
a
> table), but can be setup to execute at a specified interval.
>
> You can even get creative and by using dynamic sql, rebuild the
snapshot
> each time you update the table.
>
> I personally like snapshots over any manual update, just because the
data
> stored in the snapshot cannot get updated by accident.
>
> <michael_bialik_at_my-deja.com> wrote in message
> news:7qhb7a$52c$1_at_nnrp1.deja.com...
> > Hi.
> >
> > If you are sure that 50-100 rows it's all you will ever need -
> > go for normalized DB ( no data duplication ).
> > Use views to display totals.
> > In that case if performance will suffer - just create a table of
> > totals, recreate a view to use that table and you application
> > will remain unchanged.
> >
> > Look at Oracle 8i ( materialized views ) - it may worth a try.
> >
> > Good luck. Michael.
> >
> > In article <7qfkq5$si9$1_at_nnrp1.deja.com>,
> > erik_ostermueller_at_my-deja.com wrote:
> > > The legacy banking system that we are rewriting stores the
results of
> > many
> > > calculations in tables. For instance, when a balance is
calculated
> > for an
> > > account for a new transaction, the new balance is stored on the
> > record for
> > > the account. For flexibility and maintainability reasons, we
would
> > prefer
> > > to store only the atomic transaction data, and derive/calculate
the
> > balance
> > > (and other calculations) when requested.We know that the tradeoff
for
> > this
> > > decision is a performance hit, but don't know how big it will
be.Each
> > > calculation will involve about 50-100 records from a total of 1.5
> > billion
> > > records. This will be a 24x7 system that expects sub-second
response
> > time.
> > > Does anyone out there have some experience with this?
> > >
> > > Sent via Deja.com http://www.deja.com/
> > > Share what you know. Learn what you don't.
> > >
> >
> >
> > Sent via Deja.com http://www.deja.com/
> > Share what you know. Learn what you don't.
>
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Thu Sep 02 1999 - 13:27:37 CDT