Re: driving_site and local function call

From: Vladimir M. Zakharychev <vladimir.zakharychev_at_gmail.com>
Date: Sun, 22 Jun 2008 02:50:28 -0700 (PDT)
Message-ID: <49f058f7-702c-46c3-9be5-511bb117a7ff@w7g2000hsa.googlegroups.com>


On Jun 19, 8:57 pm, steph <stepha..._at_yahoo.de> wrote:
> oracle 10G R2:
>
> I've got this query accessing a lot of remote data via database link.
> Therefore hint DRIVING_SITE is used to make the query happen on the
> remote server. Now I've done a change, I call a local package
> function, which makes the hint being ignored.
>
> The query looks something like this
>
> select /*+DRIVING_SITE(table1) */
>          <col1>, ...
>         , local_package.myfunc()
>   from table1_at_remotedb
>     inner join table2_at_remotedb on <join condition>
>    ...
>
> When I comment the function call everything works as excpected, but
> when the function call is uncommented the query is happening locally.
> Is there any reason why the DRIVING_SITE-hint get's ignored the moment
> i call the local function? Bug or feature?
>
> Thanks,
> Stephan

Obviously, since the function is local, it can't be executed and evaluated at the remote db, so Oracle has to transfer the working set to the local db first and then call the function locally. For a distributed query to be executed remotely, all its components must be remote (tables, functions, etc.) You can copy the package to the remote db and call the function like this:

select /*+DRIVING_SITE(table1) */

          <col1>, ...
         , PACKAGE.MYFUNC_at_remotedb()
   from table1_at_remotedb
     inner join table2_at_remotedb on <join condition>

and see if the hint is obeyed again (I think it will be.)

Hth,

   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm)    http://www.dynamicpsp.com Received on Sun Jun 22 2008 - 04:50:28 CDT

Original text of this message