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/adg10pck.htm#37506 http://download-east.oracle.com/docs/cd/B10501_01/appdev.920/a96590/adg10pck.htm#20419 http://download-east.oracle.com/docs/cd/B10501_01/appdev.920/a96624/05_colls.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: 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).
