Re: hash join with remote table

That adjustment doesn’t make a difference in your numbers.

It looks like neither cost is correct so start by checking why that is. Remembering that histograms don’t really help over a DB link.

Thanks,
Andy

On Tue, Oct 4, 2022 at 7:39 AM, Laurentiu Oprea <laurentiu.oprea06_at_gmail.com> wrote:

> 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