Re: server is joining remote tables locally

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.

> thanks David, lex and Scott, it does, but it is not the optimal I=B4m
> looking for, and I'm sorry, but the example I put it is just simple.
> Let's use the following example with 4 tables that it is more like
> what I=B4m doing.
>
> select *
> from a, b, c_at_site1 c, d_at_site2 d
> where a.col1=3Db.col2 and=20
> b.col3=3Dc.col4 and
> c.col5=3Dd.col6
>
> when I used the driving_site hint the join of c and d was executed at
> the remote site
> but it request individual selects for tables a and b at the local site
> and not the join between a and b
>
> why doesn=B4t the database join the remote tables, it shouldn't be more
> eff=
> icient?
> =20
> thanks again
>
> On Thu, 10 Mar 2005 21:39:03 +0100 (CET), Lex de Haan
> <lex.de.haan_at_naturaljoin.nl> wrote:
>> did you try the driving_site hint, to force execution at the other node?
>>=20
>> cheers,
>>=20
>> Lex.
>>=20
>> > Hello oh you great masters of Database:
>> >
>> > I come to you with a humble question
>> >
>> > version 9.2.0.6 on hp-ux 11i11
>> >
>> > I have query like this:
>> > select *
>> > from a, b_at_site1 b c_at_site1 c
>> > where a.col1=3Db.col2 and
>> > b.col3 =3D c.col4
>> >
>> > I supposed that the local database should send to the remote database
>> > something like this:
>> >
>> > select * from b,c
>> > where b.col2=3D:1 and
>> > b.col3 =3D c.col4
>> >
>> > but.... nooooooo
>> >
>> > it sent
>> >
>> > select * from b
>> > where b.col2=3D:1
>> >
>> > and
>> >
>> > select * from c
>> > where c.col4=3D:1
>> >
>> > and do the join locally
>> >
>> > Any ideas about the exposed case?
>> >
>> >
>> > Thanks in advance
>> >
>> > --
>> > Alfonso Leon
>> > --
>> > http://www.freelists.org/webpage/oracle-l
>> >
>>=20
>>=20
>
>
> --=20
> Alfonso Leon
> --
> http://www.freelists.org/webpage/oracle-l
>

--
http://www.freelists.org/webpage/oracle-l