Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: server is joining remote tables locally

Re: server is joining remote tables locally

From: Alfonso León <>
Date: Thu, 10 Mar 2005 16:51:07 -0500
Message-ID: <>

Thanks lex, a good explanation, but a dissapointing one. that drives me in other direction how does the driving site knows the join order, normally in one site it depends on the statistics but how does the driving site knows the statistics in order to decide the join order. because for example if first table has 10K row hits and the second table is remote it would be possible to have 10K remote access instead of joining table one and three, having 100 rows hit and only having 100 remote access

So.. is ther a way to take remote statistics, or just use the dynamic_sample or manually decide with ordered hint the join order.

Thanks again

On Thu, 10 Mar 2005 22:27:47 +0100 (CET), Lex de Haan <> wrote:
> I see. well, you hit an architectural boundary here. note that a
> distributed datebase in Oracle is set up in such a way that all nodes are
> independent (as they should be) so the price to pay here is that you have
> to decide which node takes responsibility for the query. once that choice
> is made, you cannot dynamically hand over the responsibility to another
> node.
> of course, you can use various tricks, as some others suggested -- but the
> base line is that one of the involved nodes takes responsibility. that
> node cannot tell two other nodes to produce a join and then send the
> results back; there can only be one node doing the joining.
> think about nested loops join operations, by the way: as soon as the first
> result of the first join (C and D) becomes available, joining that result
> with the third table in the join order can (and will) start. so although
> from a conceptual view you can only join two tables at a time, from the
> implementation view you can have three join operations (joining four
> tables) at work simultaneously.
> hope this helps,
> Lex.

Alfonso Leon
Received on Thu Mar 10 2005 - 16:57:04 CST

Original text of this message