Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

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

Re: server is joining remote tables locally

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 11 Mar 2005 09:07:53 -0000
Message-ID: <009e01c52619$cf0d9960$6702a8c0@Primary>

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_at_d920@remote sal,

     sites sit,
     products_at_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_at_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

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


--
http://www.freelists.org/webpage/oracle-l
Received on Fri Mar 11 2005 - 04:11:14 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US