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.
