Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: 9i streams vs triggers

Re: 9i streams vs triggers

From: servant <mjohns1_at_uhc.com>
Date: Wed, 15 Jan 2003 06:57:08 -0600
Message-ID: <b03lrp$9cl$1@gabriel.uhc.com>


Why do I feel like someone tipped the Atlantic ocean into my mouth to give me a simple drink? It'll take me a while to wade through all this.

"DA Morgan" <damorgan_at_exesolutions.com> wrote in message news:3E1F430B.AA22D9C7_at_exesolutions.com...
> servant wrote:
>
> > You may be right. I knew Oracle was different, but it looks like I need
to
> > get up to speed.
> >
> > BTW, I searched for "MULTIVERSIONING." and got no hits. I'll keep
looking.
> >
> > "DA Morgan" <damorgan_at_exesolutions.com> wrote in message
> > news:3E1EFA12.C88758AE_at_exesolutions.com...
> > > servant wrote:
> > >
> > > > 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
> > >
> > > When you write: "I am having trouble understanding how I can design my
> > database
> > > to have data
> > > that is updated and selected simultaneously without contention." it
leads
> > me to
> > > think that you are
> > > completely unaware of Oracle architecture.
> > >
> > > In Oracle reads don't block writes and writes don't block reads. If
you
> > are
> > > designing, or your
> > > architect is designing based on knowledge of some other RDBMS it is
time
> > to
> > > stop work on head
> > > for the Oracle concepts and architecture documentation.
> > >
> > > Daniel Morgan
> > >
>
> Try MULTIVERSION rather than multiversioning and you will get hits.
>
>

http://tahiti.oracle.com/pls/db92/db92.drilldown?levelnum=2&toplevel=a96524& method=FULL&chapters=0&book=&wildcards=1&preference=&expand_all=&verb=&word= Multiversion#a96524
>
> Look at all the links and try a flashback query. Coming from any other
RDBMS it
> will ... well just be sitting down when you do it.
>
> Daniel Morgan
>
Received on Wed Jan 15 2003 - 06:57:08 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US