Re: distributed query - join problem

As others have stated, you need to play around with hints to try and avoid both network traffic from (a) pulling the big table or (b) nested loops fetching from the big table.

First rough idea:
You could try creating a view on the remote database which connects your local small table to the remote large table, with a use_hash hint. This might make the remote database pull the small table to the remote site to do the hash join there.

Your query on the local database would then be:

        select * from special_view_at_remote_database;

Jonathan Lewis

Bart PATTYN <Bart.Pattyn_at_remove.nospam.tvd.be> wrote in article <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 - 13:13:40 CST