Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Removing dublicate records in table and hostarray

Re: Removing dublicate records in table and hostarray

From: Brian Rasmusson <br_at_belle.dk>
Date: Fri, 24 Apr 1998 13:29:49 +0200
Message-ID: <354077AD.4037658F@belle.dk>


Hi,

> sss see if it is helpful to you. Query based ons single field ids
> (replace this with the original fields on which
> sss duplicate records have come. )
> sss Can you do like below and put the result in hostarray instead ofs
> processing hostarrays to mark the record
> sss whether it is duplicate or not ?

It would be a hassle to clean up the transaction table then..

Here is my detailed scenario:

  1. I have table X with 1000's of transactions
  2. I have destination table Y with millions of processed transactions

I read 1000 records from table X into my hostarray. I want to find out which records in the hostarray already exists in the destination table Y.

What i would like is a sql statement that can return a list of statuses, one for each record on my hostarray.
I would do something like this:

col1[1000]
col2[1000]
status[1000]

col1+2 is filled by my select from table x status is filled by an examination in table y which records from my hostarray already exists.
I imagine a statement returning a 0 if the record is not a dupe, and 1 if it is. I can then insert all records with status 0, and know it wont failed because of the primary key constraint.

I continue the steps above, until table X is empty. It may contain 100.000's of records, and the destination table contains millions of records, so there is no way i can have all records in memory at the same time. Thats why i read them in chunks of 1000 records.

Table X is filled by process A, while process B reads data from it. This cannot be synchronized.  

Thanks,
Brian


Brian Rasmusson, Software engineer & analyst        e-mail: br_at_belle.dk
Belle Systems A/S                                   web   : www.belle.dk
Network, Internet and communication specialists     phone : +45 59442500
------------------------------------------------------------------------
Received on Fri Apr 24 1998 - 06:29:49 CDT

Original text of this message

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