From turner@tellme.com Wed, 11 Jun 2003 22:23:12 -0700 From: David Turner Date: Wed, 11 Jun 2003 22:23:12 -0700 Subject: Re: applying transactions Message-ID: MIME-Version: 1.0 Content-Type: text/plain Cool, I'll get one of the perl guys to help me with that. Yeah I wish these were all inserts, my estimates on hardware would have been different if I had realized how costly these files would be. I really can't see a way of converting the deletes to inserts. But I have been able to convert the deletes to updates. Instead of performing a delete the update flags the record for later deletion. This allows me to use the merge which has gotten me about 3 times faster performance. However, if I could convert them all to inserts it'd be about 15 times faster. I was hitting 5000 rows a sec on inserts.. I hate the record format but it has been fun trying to figure out a way to speed things up. Thx again for the help, Dave On Wed, Jun 11, 2003 at 09:09:12PM -0700, Jared Still wrote: > > 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: 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).