Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.misc -> Re: Reports in OLTP system

Re: Reports in OLTP system

From: joel garry <>
Date: 2 Aug 2007 22:33:14 -0700
Message-ID: <>

On Jul 31, 8:09 am, Wiktor Moskwa <> wrote:
> On 29.07.2007, DA Morgan <> 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?)

Triggers not so good. In the past, when hardware was much slower, I'd do the calculation on request with storing for later use. I don't think I'd do that nowadays, but with the faster hardware the wheel that's already installed is like instant virtual DW's. Partioning would perhaps be more appropriate, but then again, we're talking organizations that are too cheap to have a second machine for reporting or DW, much less pay big licensing bucks if they don't _have_ to.

With the advent of fairly standard and sophisticated BI tools, users now just suck what they need when they need it. They get SQL-Server pricing, with the scalability of Oracle serving, even if they don't use every EE bell and whistle.

Some people are fans of VPD, I'm still reserving judgement on that, given the limitations it adds. For the situations I see, at any rate.

> 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?

Calculate every time, except for a modification where they need a snapshot to be able to reprint what was printed previously (I added a table for that, since this is a packaged app that makes a big deal about the dynamism). There are a lot more than two tables involved.


-- is bogus.
Waulking the dog, waulking the dog.
Received on Fri Aug 03 2007 - 00:33:14 CDT

Original text of this message