Re: tough question (distributed queries)...

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



Heiko Welter wrote in message <3A390266.C4979E63_at_t-mobil.de>...


>We have observed in our distributed environment a strange behavour when

>using some sql-queries. Our env. is the following:

>

>local instance (A) - only small tables

>remote instance (B) - one big table

>All instances are running Oracle8.1.6 under Solaris2.7. On instance A we

>installed a DB-Link to instance B and created a

>view on A pointing to the big table on instance B. Instance A and B are

>on different machines.

>

>When we execute our select-statement

>select /*+ driving_site(b) all_rows */ b.column1

>from big_table b

>where b.key1 in (select key1 from small_table1) -- small_table is local,

>but because of driving_site then remote

>or       b.key1 in (select key1 from small_table2) -- same

>

>We expected the small tables to be transported to the specified driving

>site and the

>query to be executed on the driving site. In the end, the result-set

>should be sent back

>to our local instance. The problem is, that it doesn't work as we

>expected it to do!

>It produced a very very large amount of network-traffic...

>

>On the other hand the statement:

>

>select /*+ driving_site(b) all_rows */ b.column1

>from big_table b

>where b.key1 in (select key1 from small_table1 union select key1 from

>small_table2)

>

>works as expected very quick.

>

>Maybe it is important that the statements produce a different

>execution-plan.

>We really don't know what to do, so any suggestions would be welcome.

>

>Thanks for your help

>cheers

>Heiko

>

>

>