Re: tough question (distributed queries)...

The easy question first - why the 'ANY'. This is because 'IN' is a defined to be equivalent to ' = ANY' and when re-writing queries (for remote or parallel execution) Oracle tends to fall back to a standard form of SQL.

How much control do you have over the SQL ? I note, by the way, that your sample SQL has 2 reference tables, and no other conditions on the large table, whereas the PLAN indicates 3 tables and other eliminating conditions.

You might try 'always_semi_join = hash' (init.ora, but can be set at SYSTEM or SESSION level). However, I don't think this will work - a semi-join is an EXISTS test, and although IN clauses can be folded into EXISTS clauses, I don't think Oracle is doing that here anyway.

You could simply try a USE_HASH(b) hint since the desirable path does HASH into (b). Again, I'm not sure it will work because there doesn't seem to be an obvious way for Oracle to turn the 'OR'ed subqueries into a single table.

The best approach is the UNION you have discovered, but presumably there is no way for the generator to generate that.

How about this:

Is there an index on the KEY1 columns on all the small tables ? If so, the 'remote' filter queries are low-cost because they are using the index. If you disable the indexes, Oracle may try to find another path to avoid doing tablescans on the small tables for each row in the large table, e.g.

    where b.key1 in (select key1 || '' from small_table1)     or ....

--
Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Practical Oracle 8i:  Building Efficient Databases

Publishers:  Addison-Wesley
See a first review at:
http://www.ixora.com.au/resources/index.htm#practical_8i
More reviews at: http://www.jlcomp.demon.co.uk/book_rev.html



Heiko Welter wrote in message <3A39CA8C.2DC4A994_at_t-mobil.de>...


>Hi Jonathan!

>Thanks for your suggestion. It seems, as if your perfectly right with your

>assuption.

>Following you can find the execution-plans for both statements. Maybe its

>important

>to say that the SQL-Statements are generated by an application. So

>it is important to know if there is a hint or a global database-option

>to force Oracle to behave in the 'right' manner.

>I already tried the hint "push_subq" but it doesn't work. Ahhh - one more

>thing:

>The "or"-query is on the remote site translated to an "ANY"-Statement -why?

>I'm really desperate, because I didn't thougt, that this would be such a

 big


>issue...

>

>Cheers

>Heiko

>

>Here is the "fast" execution-plan.

>SELECT STATEMENT (REMOTE) Optimizer=HINT: ALL_ROWS (Cost=45550 Card=1

>Bytes=34)

>  SORT (GROUP BY) (Cost=45550 Card=1 Bytes=34)

>    HASH JOIN (Cost=45545 Card=126 Bytes=4284)

>      VIEW OF VW_NSO_1 (Cost=98 Card=25619 Bytes=333047)

>        SORT (UNIQUE) (Cost=98 Card=25619 Bytes=333047)

>          UNION-ALL

>            REMOTE* (Cost=4 Card=21441 Bytes=278733)

>            REMOTE* (Cost=1 Card=4177 Bytes=54301)

>            REMOTE* (Cost=1 Card=1 Bytes=13)

>      TABLE ACCESS (FULL) OF DWH$TA_C_VERTRAG (Cost=45438 Card=106402

>Bytes=2234442)

>

>

>And this is the slow one (doesnt show a nested loop, but it seems to do

 one -


>without indices btw.)

>SELECT STATEMENT (REMOTE) Optimizer=HINT: ALL_ROWS (Cost=45506 Card=15175

>Bytes=318675)

>  SORT (GROUP BY) (Cost=45506 Card=15175 Bytes=318675)

>    FILTER

>      TABLE ACCESS (FULL) OF DWH$TA_C_VERTRAG (Cost=45438 Card=15176

>Bytes=318696)

>      REMOTE* (Cost=1 Card=1 Bytes=13)

>      REMOTE* (Cost=1 Card=1 Bytes=13)

>      REMOTE* (Cost=1 Card=1 Bytes=13)

>

>

>

>

>Jonathan Lewis schrieb:

>

>> Have you tried to run 'explain plan' against

>> the query to see what Oracle is actually

>> doing.

>>

>> In one case Oracle may have decided that the best route

>> is to fold your IN clause into an existence test, and therefore

>> do a nested loop from B for each row fetching data from

>> the two small tables.  Whereas the other case Oracle

>> may have decided to do the 'remote' union and pull it

>> across the link to drive a local hash join into the large

>> table.

>>

>> --

>> Jonathan Lewis

>> Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

>>

>> Practical Oracle 8i:  Building Efficient Databases

>>

>> Publishers:  Addison-Wesley

>> See a first review at:

>> http://www.ixora.com.au/resources/index.htm#practical_8i

>> More reviews at: http://www.jlcomp.demon.co.uk/book_rev.html