Comparing rows in 2 tables

From: Jake <Im_at_nottelling.com>
Date: Mon, 16 Dec 2002 20:57:24 -0600
Message-ID: <atm3qm$t8a$1@bob.news.rcn.net>

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 Received on Mon Dec 16 2002 - 20:57:24 CST