Re: Comparing rows in 2 tables

"Jake" <Im_at_nottelling.com> wrote in message news:<atm3qm$t8a$1_at_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

I would try to reverse the process: ie update locally and select remotely, so connect to the remote database and use a reverse link. In that case you don't have a distributed transaction (so no need for 2 phase commit) and you can use the /*+ driving_site */ hint in your subqueries.
Given the nature of your statement in that case you would probably do better to rewrite the exists subquery to an in subquery and make sure that this is executed once.

Hth

Sybrand Bakker
Senior Oracle DBA Received on Tue Dec 17 2002 - 02:31:39 CST