Re: 9i streams vs triggers

Near real-time is fine--and I am starting to think about this as a data warehouse. I'll look into asynchronous replication and dbms_job functionality.

Thanks.

"Jim Kennedy" <kennedy-down_with_spammers_at_attbi.com> wrote in message news:S%BT9.634715$%m4.204508_at_rwcrnsc52.ops.asp.att.net...
> Ahh. Sybase and DB2. Oracle is a much different beast. If you are doing
> the extract, transform,load then the reporting database is probably a data
> wharehouse. In which case the current db isn't going to cut it(due to the
> same schema). Still replication is the way to go. (If it has to be "real
> time" as opposed to a few seconds lag, then sycronus replication instead
of
> asynchronus. Then on the other end use your business logic to do your
> transform (if a view can't cut it). Just use the dbms_job functionality
in
> the other database to do the transformations. You could have it kick off
> every 5 or 10 seconds. (or if it must be "real time" or instantly, then
in
> a trigger on update, insert, delete in the table schedule a dbms_job to
run
> immediately.)
> Jim
>
> --
> Replace part of the email address: kennedy-down_with_spammers_at_attbi.com
> with family. Remove the negative part, keep the minus sign. You can
figure
> it out.
> "servant" <mjohns1_at_uhc.com> wrote in message
> news:avml6j$7k4$1_at_gabriel.uhc.com...
> > Part of the problem is that I am new to the project and do not yet
> > understand the amount of traffic this database will support. It is
> running
> > on a 2 processor RS6000 B80 under AIX which isn't the smallest machine
but
> > isn't the biggest either. Until I get a better understanding, I need to
> > trust that the technical architect is accurately describing the need for
> > reporting to hit another database. I agree that RDBMS's can handle A
LOT
> of
> > traffic. I am new to Oracle but have seen Sybase and DB2 do some
awesome
> > things.
> >
> > As I understand ETL, basically that is what I am talking about. I am
> > extracting data from one database, applying business logic to it, and
> > loading a separate database. Unfortunately, the reporting requirements
> > dictate that I do this in near real-time. Otherwise, I could write a
> script
> > that kicks off at night and the architecture becomes much cleaner.
> >
> > "Jim Kennedy" <kennedy-down_with_spammers_at_attbi.com> wrote in message
> > news:LjtT9.6039$%n.1146_at_sccrnsc02...
> > > Sybrand,
> > > I found in SQLServer I had to create a whole other machine and
replicate
> > to
> > > it just for some frigging reports! The 4 CPU 4 GIG W2K server with
50 -
> > 100
> > > concurrent (light users) users couldn't handle some basic reporting at
> the
> > > same time. Too much contention. So this "cheaper" RDBMS SQL Server
is
> > > really much more expensive than a 2 processor Sun box w/ Oracle (1
> > machine,
> > > 1 license, 1 backup, less hardware)
> > >
> > > So perhaps this is where servant is coming from. Servant, let me
assure
> > you
> > > that Oracle is a pretty robust puppy and can certainly handle OLTP and
> > some
> > > reports on the same box. If you need a Datawharehouse environment the
> > > schema is probably going to be much different anyway. In that case
you
> > are
> > > going to do ETL to someother Oracle database anyway.
> > >
> > > Jim
> > >
> > >
> > >
> > >
> > > --
> > > Replace part of the email address:
kennedy-down_with_spammers_at_attbi.com
> > > with family. Remove the negative part, keep the minus sign. You can
> > figure
> > > it out.
> > > "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 - 12:01:41 CST