Re: server is joining remote tables locally
- From: "Lex de Haan" <lex.de.haan@xxxxxxxxxxxxxx>
- To: aleon68@xxxxxxxxx
- Date: Thu, 10 Mar 2005 22:27:47 +0100 (CET)
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@site1 c, d@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@xxxxxxxxxxxxxx> 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@site1 b c@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
>> > --
>> > //www.freelists.org/webpage/oracle-l
>> >
>>=20
>>=20
>
>
> --=20
> Alfonso Leon
> --
> //www.freelists.org/webpage/oracle-l
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
