From oracle-l-bounce@freelists.org Thu Mar 10 16:31:05 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j2AMV5an003661 for ; Thu, 10 Mar 2005 16:31:05 -0600 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j2AMV5em003657 for ; Thu, 10 Mar 2005 16:31:05 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 8CA447ED61; Thu, 10 Mar 2005 16:29:30 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 25001-09; Thu, 10 Mar 2005 16:29:30 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 0997C7F00A; Thu, 10 Mar 2005 16:29:29 -0500 (EST) Message-ID: <4136.63.174.49.10.1110490067.squirrel@webmail.tiscali-business.nl> In-Reply-To: <83a585ac05031013017fe3b902@mail.gmail.com> References: <83a585ac05031012093078ed80@mail.gmail.com> <3894.63.174.49.10.1110487143.squirrel@webmail.tiscali-business.nl> <83a585ac05031013017fe3b902@mail.gmail.com> Date: Thu, 10 Mar 2005 22:27:47 +0100 (CET) Subject: Re: server is joining remote tables locally From: "Lex de Haan" To: aleon68@gmail.com Cc: oracle-l@freelists.org User-Agent: SquirrelMail/1.4.3a MIME-Version: 1.0 Content-type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-archive-position: 17171 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: lex.de.haan@naturaljoin.nl Precedence: normal Reply-To: lex.de.haan@naturaljoin.nl X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-Spam-Checker-Version: SpamAssassin 2.60 (1.212-2003-09-23-exp) on air891.startdedicated.com X-Spam-Status: No, hits=0.0 required=5.0 tests=AWL autolearn=ham version=2.60 X-Spam-Level: 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 > 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 >> > -- >> > 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