Before trying a rewrite - or going though the painful process of trying to
interpret a CBO trace file, you could pursue three ideas:
a) Does Oracle consider subquery unnesting to be illegal ... try adding an
/*+ unnest */ hint to the subquery itself to see if it is possible (even it
produces a very bad plan as a result)
b) Does Oracle think the subquery will happen only a very small number of
times - if so address address the cause of of the bad cardinality estimate
before chasing the subquery error
c) Has Oracle run the subquery at the earliest possible moment or the
latest possible moment - if there are multiple subqueries have they been
run in the wrong order: would it help to change the timing of when the
subquery runs.
Regards
Jonathan Lewis
On Wed, 26 May 2021 at 15:29, Amit Saroha <dmarc-noreply@xxxxxxxxxxxxx>
wrote:
Hi,
In one of the queries TKProof shows, most time is spent in accessing a
table thousands of times inside NOT EXIST condition.
I am looking for your inputs if it's a promising idea to replace NOT EXIST
with the LEFT OUTER JOIN and IS NULL condition?
Any inputs in this regard are appreciated.
Regards,
Amit S
