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).
