Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: distributed query - join problem
Try it with the /*+ ordered */ -hint, to
tell the optimizer to use your small, local table as driving site.
The ORDERED hint, when used with nested loops joins, influences the order in which tables are joined - that is, the structure of the loops used by the join. If you use this hint, you need to be certain that the relative distribution of values within the joined tables will not change dramatically over time.
Reiner
Bart PATTYN schrieb in Nachricht <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 - 08:02:51 CST