Re: distributed query - join problem

Bert,

There's no good solution to this. If you use a nested loops join (NL) with the local table as the driving table, then you'll be doing tons of network access. Usually when this occurs, the best performance solution involves forcing the remote table to be the driving table for the join, or using a Merge Join instead. Oracle's selection of the remote table as the driving table (which happened for your query) is actually a good plan.

Since you have such a large remote table, you'll need to evaluate how volatile the table's data is. If the remote table's data is very static, you may be able to do a (hopefully) selective CREATE TABLE AS SELECT in your local database, create the index on the local copy of the remote data, and then query the tables locally. You didn't say what percentage of the remote table is used by the query, but for selective queries the temporary replication method may be an effective alternative.

hth.
Kevin
http://www.kevinloney.com

Bart PATTYN wrote in message <366E677E.8D59A7A2_at_remove.nospam.tvd.be>...
>I have a problem with querying a remote table. This is quite a large
>(1M records, 300MB) table with a 4 column (1 number, 3 varchars) primary
>key.
>
>I want to fetch into a local copy only those records whose PK's I have
>stored in a local look-up table (about 15.000). The problem is, when
>joining the two
>tables, Oracle decides to send over the big table from the other side,
>eating bandwith and failing on temp tablespace. So I currently fetch
>them
>one-by-one trough a LOOP'ed cursor on my lookup-table, but then of
>course
>I generate more network overhead than I actually collect data.
>I'm sure I'm overlooking a simpler and more performant way to do this.
>Both DB's are 7.3.x.
>
>Thnx for any input.
>
>
Received on Wed Dec 09 1998 - 12:10:53 CST