Re: Best way to compare records ?

Have you tried a straight SQL compare? Assuming two tables, your main data table and your "MASTER" records table. Identical fields, especially primary keys. Then let the ORACLE Optimizer determine the fastest way to get it:

Select main.rowid, master.rwid
where main.key(+) = master.key
and nvl(main,field1,default1) <> master.field1 ...

result set is the rowids of what needs to change and the master record to supply the correct values. Or when there is no matching main record, the result is NULL for the main.rowid for that master record (so you know what data is missing.

Try and see if it is any faster.

CrazyIvan wrote:
>
> The problem is not getting it to work, but finding the most efficient
> method. At the moment i am
> fetching the new (master) records into a cursor. In the body of this cursor
> i locate the correposponding
> record (if any), wich i compare field by field using the NVL-function to
> avoid having to compare a value with a null-value.
> I find that this procedure works fine, but runs very slow.
> Maybe it's the only way?
>
> Thanx anyway..
>
> "Ed Prochak" <edprochak_at_magicinterface.com> wrote in message
> news:3BD39C72.AF255E4F_at_magicinterface.com...
> >
> >
> > CrazyIvan wrote:
> > >
> > > What would be the best way to compare records field by field in PL/SQL?
> > > I have to update a table with several thousand records, some new, some
> the
> > > same, but some altered.
> > > Every record has to be compared field by field (not all fields, but most
> of
> > > them) to detect changes.
> > >
> > > Thanx ia.
> >
> > I'd say the answer is: it depends!
> >
> >
> > I don't believe there is an answer specific to ORACLE. This is really
> > dependent on your application data. I say this from the experience of
> > working thru these issues right now on my current assignment.
> >
> > 1. If you have one source that you know is correct, then is should be a
> > simple procedure.
> >
> > 2. If your problem is like mine, identifying which of multiple systems
> > have the right data, then you may be stuck with lots of manual data
> > review.
> >
> > 3. If you problem is merely the issue of applying a delta, then it also
> > should be easy.
> >
> > I know this doesn't really solve your problem, but remember, I'm stuck
> > in situation #2!

Ed Prochak
Magic Interface, Ltd.
www.magicinterface.com Received on Wed Oct 24 2001 - 00:40:59 CDT