Re: 9i streams vs triggers

Sybrand,

I am having trouble understanding how I can design my database to have data that is updated and selected simultaneously without contention. The update will lock the row and block the select and vice versa, right? Can you expand . . .?

What I meant by replication is logical hot standby so I think we agree there. That solves the contention issue but does not address the complexity issue. I agree that this is not a pretty solution, but I need a way to take the data from one data model, massage it with existing code (so I don't have to develop/maintain it in two places), and write it to another "reporting" database at near real time. Those requirements don't leave me with any pretty solutions that I can think of. I am no guru, though, so I certainly welcome any suggestions.

RAC, as I understand it, will give me scalability which, again, solves the performance/contention issue but does not address the complexity issue.

You mentioned "Advanced Replication". I need to find out what that is (I am new to Oracle but learning fast). If it can call Java methods it just might work. Can you offer opinions on this versus triggers or streams?

I'm off to tahiti (.oracle.com) . . .

"Sybrand Bakker" <gooiditweg_at_nospam.demon.nl> wrote in message news:77pr1v42q0kef48ad29cacqjvsinbqp6vl_at_4ax.com...
> 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 Fri Jan 10 2003 - 08:14:36 CST