Re: driving_site and local function call

On Jun 23, 10:17 pm, steph <stepha..._at_yahoo.de> wrote:
> On Jun 22, 11:50 am, "Vladimir M. Zakharychev"
>
>
>
> <vladimir.zakharyc..._at_gmail.com> wrote:
> > 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
>
> ok, i see. So this is a kind of a one-way street: I can query remote
> tables/packages ... but a local one cannot be called by the remote
> server -- I was hoping that this would happen "somehow" ...
>
> ok, but then I could create a function on the remote site that would
> just do this:
>
> create function return sometype as
> begin
>   return call_to_local_function_at_local_db();
> end;
>
> this should make it work, shouldn't it?
>
> thanks,
> stephan

Well, you can at least try. Not sure it will work as expected, and in any case it won't help performance: for every remote invocation there will be an additional network roundtrip to your local db, which you want to avoid. The best solution would be to replicate the function (or package) itself to the remote db and keep it in sync with local copy. Regrettably, this can't be done automatically, so you'll have to manually synchronize remote copy whenever local copy is modified.

Regards,

   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm)    http://www.dynamicpsp.com Received on Tue Jun 24 2008 - 01:12:05 CDT