Re: 9i streams vs triggers

As others have already mentioned, dmls won't block selects. select queries will return the committed data at that instant.

As for massaging the data and write to another database, consider plsql code and dml on remote database using remote database links.

If a lot of tables are involved, the streams can do a lot of task for you automatically - you can even specify transform function ( I believe) while setting up your replication rules.

hope it helps.
SA

"servant" <mjohns1_at_uhc.com> wrote in message news:avmkgh$6kq$1_at_gabriel.uhc.com...
> 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 - 10:38:59 CST