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

Home -> Community -> Mailing Lists -> Oracle-L -> Re: applying transactions

Re: applying transactions

From: Jared Still <jkstill_at_cybcon.com>
Date: Wed, 11 Jun 2003 21:42:53 -0700
Message-ID: <F001.005AFC8C.20030611210451@fatcity.com>

Well, you *could* use Perl.

You can then set the RowCacheSize attribute on the handle, and Perl will send data to Oracle in arrays ala Sqlplus.

>From your example it appears that you may currently
be doing individual INSERT statements for each record in your log file ( or DELETE or whatever ) and you really need to change that.

But I think you know that already.

Jared

On Wednesday 11 June 2003 19:20, David Turner wrote:
> NP, yeah I've used bulk binds and they're great but I wish they offered
> conditional logic within the forall statement. If I could perform
> conditional logic or assignment of values to variables within the forall
> it'd really make my life easier.
>
> In my situation a merge select will perform as well if not better than a
> bulk bind. At least I think this is the case:)
>
> Thx for the links and help, Dave
>
> On Wed, Jun 11, 2003 at 05:29:42PM -0700, [EMAIL PROTECTED] wrote:
> > Sorry, guess I wasn't clear.
> >
> > Bulk loading is done with PL/SQL, not sql loader.
> >
> > I should have referred to 'bulk binds'.
> >
> > Here are some URL's that may help.
> >
> > http://download-east.oracle.com/docs/cd/B10501_01/appdev.920/a96590/adg10
> >pck.htm#37506
> >
> > http://download-east.oracle.com/docs/cd/B10501_01/appdev.920/a96590/adg10
> >pck.htm#20419
> >
> > http://download-east.oracle.com/docs/cd/B10501_01/appdev.920/a96624/05_co
> >lls.htm#28332
> >
> > Jared
> >
> >
> >
> >
> >
> >
> > David Turner <[EMAIL PROTECTED]>
> > 06/11/2003 05:02 PM
> >
> >
> > To: [EMAIL PROTECTED]
> > cc: [EMAIL PROTECTED]
> > Subject: Re: applying transactions
> >
> >
> > Thx Jared,
> >
> > But I don't see how I could use loader to perform a delete. Sad to say
> > but this
> > isn't the typical load that I'm used to.. The load files contain inserts
> > and
> > deletes. I could set up a temporary table to load into which has a
> > trigger
> >
> > fire on delete to remove rows from the destination table and on an insert
> > inserts
> > the row into the destination. But, I would think this would be about as
> > slow because
> > the trigger would have to find each record for deleting.
> >
> > I also needed to add that each insert and delete was an entire record
> > with a flag
> > showing the transaction type (I= insert O=out/delete).
> >
> >
> > I think I have a cool solution though. Here's an example :
> >
> > This is the table that contains the transactions
> > create table test1(id number(4),trans_no number(4), trans char(1), field
> > varchar2(8));
> >
> > Here are some example transactions:
> >
> > insert into test1 values(1,101,'I','A');
> > insert into test1 values(2,102,'I','A');
> > insert into test1 values(3,103,'I','A');
> > insert into test1 values(4,104,'I','A');
> > insert into test1 values(5,105,'I','A');
> >
> > insert into test1 values(1,106,'0','X');
> > insert into test1 values(2,107,'O','X');
> > insert into test1 values(3,108,'0','X');
> > insert into test1 values(4,109,'I','B');
> > insert into test1 values(5,110,'I','B');
> >
> > insert into test1 values(5,115,'0','X');
> > insert into test1 values(4,114,'I','C');
> > insert into test1 values(3,113,'I','C');
> > insert into test1 values(2,112,'I','C');
> > insert into test1 values(1,111,'I','C');
> >
> > Here is the select to get the last change performed on a row
> >
> > select a.id,a.trans_no, a.trans, a.field
> > from
> > test1 a,
> > (select id,max(trans_no) trans_no from test1 group by id) b
> > where
> > a.trans_no=b.trans_no;
> >
> >
> > Here would be the result:
> >
> > ID LINE_NO T FIELD
> > ---------- ---------- - --------
> > 1 111 I C
> > 2 112 I C
> > 3 113 I C
> > 4 114 I C
> > 5 115 0 X
> >
> >
> > I then merge this result set with the destination table. I haven't found
> > any
> > problems yet and I'm fairly certain I'll hit around 900 trans per sec.
> >
> > Thanks,
> > Dave
> >
> > On Wed, Jun 11, 2003 at 04:43:32PM -0700, [EMAIL PROTECTED] wrote:
> > > If you're on 8i+ you can use bulk loading. It could save you a
> > > lot of time on large loads such as this.
> > >
> > > Jared
> > >
> > >
> > >
> > >
> > >
> > >
> > > David Turner <[EMAIL PROTECTED]>
> > > Sent by: [EMAIL PROTECTED]
> > > 06/11/2003 04:04 PM
> > > Please respond to ORACLE-L
> > >
> > >
> > > To: Multiple recipients of list ORACLE-L
> >
> > <[EMAIL PROTECTED]>
> >
> > > cc:
> > > Subject: applying transactions
> > >
> > >
> > > I've got a project where I get daily log files with inserts and deletes
> >
> > to
> >
> > > keep a
> > > table current. I've set up an external table which contains the logs
> > > and
> >
> > a
> >
> > > stored
> > > procedure reads from it and inserts or deletes from the table
> >
> > accordingly.
> >
> > > Note
> > > one insert or delete per iteration. They're not bulked.
> > >
> > > The problem is it is running way too slowly. I'm running about 300
> > > transactions
> > > a second and believe the slow time has to do with context switching.
> >
> > Merge
> >
> > > won't
> > > work because it can't handle a record being changed multiple times in
> >
> > the
> >
> > > transaction log/external table. When I run inserts only I'm inserting
> > > about 5000
> > > rows a second, but understand the deletes would slow it down
> >
> > considerably.
> >
> > > Keep in mind all the records have to be executed sequentially because
> > > we're just
> > > applying a log file.
> > >
> > > Right now I'm trying to figure out a scheme to perform all the inserts
> > > that don't
> > > exist in the destination table, then all deletes, and then the
> > > remaining
> > >
> > > inserts
> > > but thought I should just send an email to see if someone had a better
> >
> > way
> >
> > > of
> > > getting me the transaction rate I need, about 1000 rec/s.
> > >
> > > Thanks, Dave
> > >
> > > --
> > > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > > --
> > > Author: David Turner
> > > INET: [EMAIL PROTECTED]
> > >
> > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > > San Diego, California -- Mailing list and web hosting services
> > > ---------------------------------------------------------------------
> > > To REMOVE yourself from this mailing list, send an E-Mail message
> > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> > > the message BODY, include a line containing: UNSUB ORACLE-L
> > > (or the name of mailing list you want to be removed from). You may
> > > also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jared Still
  INET: [EMAIL PROTECTED]

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Wed Jun 11 2003 - 23:42:53 CDT

Original text of this message

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