hash join with remote table
Date: Tue, 4 Oct 2022 17:38:40 +0300
Message-ID: <CA+riqSUeLWxSoXDuRD5wGfeU2hUT_dZFgrYEcpep_gNTswktiQ_at_mail.gmail.com>
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 Received on Tue Oct 04 2022 - 16:38:40 CEST