Re: Why does adding simple grouping slow dblink query to a crawl?

On 27 Nov 2002, willhandley_at_comcast.net wrote:
> I know there are problems with a DBLINK, but this query should'nt have
> them. Yet, while it runs instantly on the machine where the data
> resides, it takes over 2 hours when going through a dblink.
>
> The inner select is where all of the heavy work takes place, multiple
> joins, grouping, counting, etc. Yet, if I run the inner query alone
> through the dblink, it comes back as quickly as when run on the remote
> machine. All of the tables reside on the remote database-I think
> Oracle is smart enough to run the query there. BUT, and here is the
> puzzling part, when I include the outer select, it takes hours to run.
> Yet, the outer select is merely grouping the few hundred rows returned
> by the complex inner select, so you wouldn't
> think adding it would have such a detremental effect. I've tried all
> combinations of hints, to no avail.
>
> So the question is, why does the addition of this simple outer select
> have such an effect when going through a dblink? Oracle 8.1.7, 64-bit
> servers, same versions on both servers.

Try creating the inner select as a view and then joining to that view.

My guess is that Oracle is bringing each table in the inner select across the network, putting them into RAM on the driving site and then doing the join. Bottling all this up into a view and then querying that should make the remote query run as expected.

-- 
Galen deForest Boyer
Sweet dreams and flying machines in pieces on the ground.