Re: sql tuning

insert into fctab
as select * from fctab2
where mpc in
(
select mpc from fctab
minus
select mpc from fctab2
)

maybe ??

Daud wrote:

> 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 - 23:45:27 CDT