sql tuning

Hi

I have 2 identical tables. I am trying to copy records from one table to the other if the record has not been copied yet. Here is the sql.

select *
from fctab remote
where not exists
(select 'x'
 from fctab2 local
 where local.MPC = remote.MPC
)

MPC is the PK on each table.

The execution plan looks like this.

Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE    1 0 FILTER

   2    1     TABLE ACCESS (FULL) OF 'FCTAB'
   3    1     INDEX (UNIQUE SCAN) OF 'FCTAB2MPCIDX' (UNIQUE)

My question is: Is there a better way to write the SQL so that it does not do a FTS on the remote table (fctab)? I have tested the query and it is very slow especially when the remote table has to be accessed over a db link.
Any suggestion?

BTW, I do not have statistics on any of the tables. So, it should be using RBO.
CBO is not yet supported by the app.

rgds
Daud Received on Tue Aug 20 2002 - 22:27:49 CDT