Re: 9i streams vs triggers

On Thu, 9 Jan 2003 13:06:43 -0600, "servant" <mjohns1_at_uhc.com> wrote:

>I have been tasked with developing a reporting architecture for an
>application that has been developed over the last few months. I have been
>told there are 2 main concerns: performance (contention) and complexity of
>the data model. I am thinking of using Oracle's replication to maintain a
>(near) real-time replica. That (basically) resolves the contention issue.
>The complexity issue is more difficult.
>
>I am thinking about using 1) streams against the redo logs or 2) triggers to
>pull the data from the replica, apply business logic to it, and populate a
>"logical" data model on which reports can be generated. I would like to use
>the same abstraction layer to go from the physical to the logical data model
>as the application interface uses (Java objects with EJBs). In other words,
>I need the code inside the streams or triggers to call Java methods (ie:
>loadCustomerData(cust_id)) which would get the data and apply any business
>logic and then populate the "logical" database.
>
>In essence, I think I am being asked to create a data warehouse, but I
>haven't convinced myself of that yet.
>
>Anyway, here is my question: Does anyone have an opinion on which method
>(streams or triggers) would tax my replica database more--from a CPU,
>network, and/or contention perspective? Any opinions from other
>perspectives which option is better (maintenance, etc).
>
>

First of all I would like to question the need of a replica database for reporting purposes. As for contention, this is simply not an issue, if the application has been designed properly. Your post has a distinct smell of finding a 'hardware solution' read 'workaround' for what is in reality a software design problem. If you really think you need a hardware solution you could just as well consider RAC, to have multiple servers running on the same instance.
As for your option 1) you seem to be trying to re-invent the standby database technology . One simple advice: don't. You would never get it work, and provided your organisation has still a bag of $$ floating arond: get Oracle Enterprise Edition, set up a standby database which will use the redolog to keep the standby database up-to-date and start living
As for your option 2) this seems like yet another solution which is going to be designed in a bike-shed. Replication can be accomplished by setting up the Advanced Replication option, and designing 'home-grown' solutions will definitely make you bald sooner and cost more.

Regards

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Thu Jan 09 2003 - 15:16:44 CST