Re: driving_site and local function call

On Thu, 19 Jun 2008 09:57:29 -0700 (PDT), steph <stephan0h_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

How about

select remo.*, local_package.myfunc()
from (select /*+DRIVING_SITE(table1) */

         <col1>, ...
    from table1_at_remotedb
    inner join table2_at_remotedb on <join condition>) remo

Knowing Oracle I would not be surprised though if the records are first written to TEMP and then the function is applied.

Regards,
Jaap Received on Wed Jun 25 2008 - 04:24:33 CDT