Re: hash join with remote table

THanks guys.

THat adjustment is not a percentage? I considered that the adjustment is done like this: (1-0.005859) * 27.180501 = 27.021 -> thus the hash join

I was thinking if I can do something (other than the obvious patch/profile)

În mar., 4 oct. 2022 la 17:58, Andy Sayer <andysayer_at_gmail.com> a scris:

> 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