| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: applying transactions
Depending on the volume of the changes it may be worthwhile to drop and then recreate indexes on the destination table. Indexes are going to slow the transaction rate down considerably.
One way to partly achieve this goal would be to perform all inserts into a temporary table which has no indexes then use a single statement to copy the contents of this table into the main table. Applying deletes and updates (if any) could be more difficult though since a given record could be inserted, then deleted, and perhaps even re-inserted during a single log file. Dropping indexes on the main tablemay be easier to implement.
The "best" approach probably depends on a few factors including: what is the size of the destination table, what is the size of the typical log file, what are the availability requirements of the table, can a single record appear multiple times in the one log file, etc.
[EMAIL PROTECTED]
ys.com To: Multiple recipients of list
ORACLE-L <[EMAIL PROTECTED]>
Sent by: cc:
[EMAIL PROTECTED] Subject: Re: applying transactions
.com
12/06/2003 10:45
Please respond to
ORACLE-L
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).Received on Wed Jun 11 2003 - 20:11:00 CDT
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Privileged/Confidential information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone. In such case, you should destroy this message and kindly notify the sender by reply e-mail or by telephone on (61 3) 9612-6999. Please advise immediately if you or your employer does not consent to Internet e-mail for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of Transurban City Link Ltd shall be understood as neither given nor endorsed by it. <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Richard 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).
 |
 |