Re: dblink: functions enforce tablescan

Check your explain plan again. In particular, examine the OTHER column of the PLAN_TABLE. In this column, you'll see the SQL statement that is being sent to the remote database. Now go to the remote database and do an explain plan on that statement. This will tell you if an index is being used. The results of this statement are then sent back to the local database. The local database then has no choice but to do a full scan on the result set that has been returned from the remote database. But this does not mean that an index was never used.

HTH,
Brian

Jan-Erik Rosinowski wrote:
>
> hi,
>
> how can we prevent the oracle optimizer from making complete
> tablescans when using data-base-links?
>
> create table a (b number primary key);
> create function f return number as begin return 42; end;
> select * from a_at_dblink where b=f;
>
> the plans differ whether one uses a local or a remote-table..
>
> we originally use packaged functions but even pragma restrict
> reference(f,rnds,rnps,wnds,wnps) does not help.
>
> any ideas why this happens and what we could do?
>
> ciao, jan
>
> http://www.rsp.de/
>
> rs&p-Dossier: Software zur Erstellung technischer Dokumentationen
> und Schriftgutes in Verwaltung und Industrie.
 

-- 
========================================
Brian Peasland
Raytheons Systems at
  USGS EROS Data Center
These opinions are my own and do not
necessarily reflect the opinions of my 
company!
========================================