Re: hash join with remote table

Hi Laurentiu,

I wonder if the estimates are not the more important concern. If 1 would be correct in both places and can not see one hour runtime.

Thus, correct the estimates and the plans should fall into place.

Apart from that: the overhead of fetching rows over a network was so far never considered in the costing. Maybe that has changed now.

Thanks

Lothar
Am 04.10.2022 um 16:38 schrieb Laurentiu Oprea:
> Hello everyone,
>
> DB version 19.5
>
> I have a situation (which I think I saw in the past) where in the
> execution plan the result of a NL join, estimated to cardinality 1, is
> hash joined with a remote table (estimated similarly to cardinality 1).
>
> The problem is, because of HASH join(right outer), the join predicate
> is not pushed in the remote query..and as a consequence the remote
> query is taking 1 hour. With NL, the remote query will take just a few
> seconds (because of the added where clause).
>
> Looking into 10053 looks like the cost for NL is 27.154647 and cost
> for Hash is 27.180501, so in theory NL should be used but just above
> best join method I can see: "Cost adjustment for NL join with remote
> table 0.005859" and then HASH is used.
>
> Can someone help me understand this "Cost adjustment for NL"?
>
> Thank you.
>
>

--
http://www.freelists.org/webpage/oracle-l