Re: performance question: sum values vs. store calculations

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.
Received on Tue Aug 31 1999 - 23:51:43 CDT