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: Craig Munday <Craig.Munday_at_ecard.com.au>
Date: Thu, 12 Jun 2003 23:41:55 -0700
Message-ID: <F001.005B0E63.20030612230918@fatcity.com>


When you say transactions, do you really mean transactions or are you referring to the number of rows being inserted?

-----Original Message-----
Sent: Thursday, 12 June 2003 11:00 AM
To: Multiple recipients of list ORACLE-L

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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Craig Munday
  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 Fri Jun 13 2003 - 01:41:55 CDT

Original text of this message

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