tough question (distributed queries)...

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 Received on Thu Dec 14 2000 - 11:24:54 CST