Re: Replace Not Exist with Left Outer Join and Is Null

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_at_freelists.org> 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
>
>
>

--
http://www.freelists.org/webpage/oracle-l