Re: Reports in OLTP system

On 29.07.2007, DA Morgan <damorgan_at_psoug.org> wrote:
> Wiktor Moskwa wrote:
>>
>> Well, I want to learn how such things are done.
>> Are materialized views or advanced queuing right tools
>> here or not.
>
> To determine that would be a full scale research project. They might
> be and then again they might not be. I know organizations doing it
> with Data Guard replication to a logical standby so it very much
> depends.

Thanks for a reply. Let me change my question a little. I wonder what is a usual practice of serving aggregate data in a standard database (not a warehouse, used to store current data). By aggregate data I mean for example monthly sales summary.

The example environment is a web application with 2000 users (each is a different <very> small business, not related to others, with distinct subset of data). How to provide them with effective up-to-date summaries? (triggers? MVs? queuing? calculation on request and storing for later use?)

And a sub-question: with two tables INVOICE (parent) and INVOICE_ITEM (child) - do you usually keep sum of values from child rows in parent row or calculate in application every time?

Thanks in advance.

-- 
Wiktor Moskwa