Re: Comparing rows in 2 tables

Jake wrote:

> Hi, I'm trying to merge data from 2 tables. One table exists locally,
> and the other lives across the network on another box. Each table has ~1M
> rows. I
> want to update the remote table with the values from the local table
> where the key is the same but the data is different.
>
> My example has been (greatly) simplified. the table is created on both
> database like:
>
> create table TABLEA
> (
> ID Number not null,
> DATA Varchar2(200) not null,
> constraint PK_TABLEA primary key (ID)
> )
> /
>
> when I do:
> select count(*)
> from tablea local,
> tablea_at_dblink remote
> where local.id = remote.id
> and nvl(local.data,'1') != nvl(remote.data,'1')
>
> I get my result back pretty quickly, ~2 mins (with a count of 212
> different rows).
> But when I update using a correlated subquery it takes 30ish mins.
>
> update tablea_at_dblink remote
> set (data) =
> (select data from tablea local
> where local.id = remote.id)
> where exists
> (select 1 from
> tablea local2
> where local2.id = remote.id
> and nvl(local.data,'1') != nvl(remote.data,'1')
> )
> /
>
> I think there has got to be a way to speed it up. I want to drive the
> update off the small set of different rows. I can do it programmatically
> but I'd like a SQL solution.
>
> Thanks
> Jay

Why? Why not use the solution you have in hand that works?

Daniel Morgan Received on Mon Dec 16 2002 - 23:09:37 CST