Re: dblink: functions enforce tablescan

You have to create the function on the remote database:

v734> create database link refold using 'refold'; Database link created.
v734> create table t (col number primary key); Table created.
v734> create function f return number as begin return 42; end;   2 /
Function created.
v734> explain plan for
select * from t_at_refold where col=f;

SELECT STATEMENT
  FILTER
    REMOTE (REFOLD.WORLD) (SELECT "COL" FROM "T" T) v734> explain plan for
select * from t_at_refold where col=f_at_refold;

SELECT STATEMENT REMOTE
  INDEX UNIQUE SCAN MMA$EP34087.SYS_C008074

--
Have a nice day
Michel


Jan-Erik Rosinowski <spamfilter_at_rosinowski.de> a écrit dans le message :
38ff9ac3.20945598_at_News.CIS.DFN.DE...


> 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.