Re: server is joining remote tables locally
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 11 Mar 2005 09:07:53 -0000
Lex,
I don't think your comments are complete.
I've had the following test statement running
against 8i and 9i, and the execution path
shows a remote join.
Unfortunately, 10g switched to doing the bad
thing - fetching sales and products data in two
separate steps.
Note the complete absence of hints.
select
sale_date,product, site, qty, profit
from
sales@d920@remote sal,
sites sit,
products@d920@remote prd
where
sit.id = sal.site
and prd.id = sal.product
and prd.promoted > to_date('&m_test_date','dd-mon-yyyy')
;
This was the plan from 9.2.0.1
0 10 SELECT STATEMENT (choose) Cost (10,6939,333072)
IO_Cost (13,,)
1 0 1 HASH JOIN Cost (10,10000,350000) IO_Cost (10,,)
Access ("SIT"."ID"="A1"."SITE")
2 1 1 INDEX (analyzed) UNIQUE TEST_USER SI_PK (full scan)
Cost (1,72,360) IO_Cost (1,,)
3 1 2 REMOTE SERIAL_FROM_REMOTE Cost (8,10000,300000)
NODE/OTHER values:
D9201.JONATHAN_LEWIS.CO.UK@REMOTE
SELECT
"A1"."SALE_DATE","A1"."PRODUCT","A1"."SITE","A1"."QTY","A1"."PROFIT","A1"."SITE","A1"."PRODUCT","A2"
."ID","A2"."PROMOTED" FROM "SALES"
"A1","PRODUCTS" "A2" WHERE
"A2"."PROMOTED">TO_DATE('01-jan-2000') AND
"A2"."ID"="A1"."PRODUCT"
I haven't re-run the tests recently.
I think the biggest problem with distributed
joins is that Oracle does not appear to allow
ANY cost for the remote fetch; so whatever
plan is cheapest for all tables being local will
be the plan that is used for the distributed join.
Inevitably, because of the 'left hand descent' (or whatever
they call that picture in the manuals), anything with a
nested loop is likely to do something silly.
As far as the OP is concerned, it is possibly that
the inline view mechanism suggested by David
would work if it included a /*+ no_merge */
hint in the in-line select.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/seminar.html
Public Appearances - schedule updated March 9th 2005
----- Original Message -----
From: "Lex de Haan" <lex.de.haan@xxxxxxxxxxxxxx>
To: <aleon68@xxxxxxxxx>
Cc: <oracle-l@xxxxxxxxxxxxx>
Sent: Thursday, March 10, 2005 9:27 PM
Subject: 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@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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
