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

Hi Amit,

Usually CBO can easily transform "not exists()" to hash anti-join, so you don't need to rewrite your query, but need to check your why CBO chooses that execution plan.
You can trace it with
ALTER SESSION SET EVENTS 'trace[rdbms.SQL_Optimizer.*][sql:*your_sql_id*]';

On Wed, May 26, 2021 at 5:29 PM 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
>
>
>

-- 
Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE Associate
http://orasql.org

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