Re: hash join with remote table

Hi Laurentiu

Maybe that adjustment is for covering additional overhead due to multiple calls of the remote query by network, since every loop is a new remote call with bind variables by dblink.

Best regards,
Sayan Malakshinov
Oracle performance tuning expert
Oracle Database Developer Choice Award winner Oracle ACE
http://orasql.org

On Tue, 4 Oct 2022, 15:39 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