Re: Client/Server query strategy

chuckh_at_ix.netcom.com (Chuck Hamilton) wrote:

[deletia]

>I'd just be sure to design my aggregate table in such a way that it was
>simple and fast to update. If it was a summary table, just add the new
>total to the appropriate aggregate column upon insert, subtract on
>deletion, and both on an update. It shouldn't take that long should it?
>For averages you'd have to keep a row count somewhere in the aggregate
>table to insure speedy recalculation.
>

Er, the issue isn't the time it takes to update the aggregate table, it's the time it takes to _calculate_ the aggregated value with which to update the aggregation table that counts. With large database tables, the time to calculate a sum/average or whatever can be very significant.

>Is it possible to keep the transaction open until the aggregate table
>is updated too? (I'm not real familiar with how Oracle trigger's work.

Yes, but if it takes 3 minutes to calculate the sum of a column on a multi-million-row table, do you _really_ want to make the updater waith that long? (Maybe you do, as they say, "your mileage may vary.")

--
Bill Meahan,  Senior Developer  |        wmeahan_at_ef0424.efhd.ford.com
Electrical & Fuel Handling Division, Ford Motor Company
Opions expressed herein are those of the author and in no way represent
any official statement or opinion of Ford Motor Company