Re: SQL, remote queries and joins

Emmanuel Baechler <ebaechle_at_hospvd.ch> wrote in article >>...
>

<<<<Distributed Query Removed>>>>
Now, my question is the following: in the case of a join including tables of different instance, what is done on each instance and what is tranferred between instances? Il looked both at the ORACLE DBA Handbook and at the > ORACLE PL/SQL Programming guide (both from ORACLE Press) and I did not find a precise answer to this question.

Second question, is there a way to determine (and control) where given parts of a query will be executed?

Finally, is there a written documentation for this question? If it is the case, what is its reference?
<<<<<

There is no precise answer to your first question because it varies based on the circumstances. In general Oracle will break the query into components and dispatch these component queries to the local and remote platforms. If the key columns values are provided to a remote table then only the result of this keyed select is brought back to the local platform, but if the key values are unknown then Oracle will fetch the entire remote table and perform the join locally. You can use explain plan to see the local and remote components. I think the remote portion goes into the other column?

The (I think undocumented in 7.3) hint driving_site(table_name) can be used to influence the distributed query.

I would expect that you best bet for really getting good information on these type of queries would be in the Oracle7 Server Distributed Systems Manual Vol. 1 and 2.

The book Oracle SQL High Performance Tuning by Guy Harrison has several example explain plans on distributed queries, and lists the hint. Received on Fri Sep 18 1998 - 00:00:00 CEST