Re: Replace Not Exist with Left Outer Join and Is Null
Date: Wed, 2 Jun 2021 16:12:37 +0000 (UTC)
Message-ID: <1971169072.1512775.1622650357675_at_mail.yahoo.com>
Jonathan,
On Thursday, May 27, 2021, 05:05:55 AM EDT, Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:
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 errorc) 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.
RegardsJonathan Lewis
On Wed, 26 May 2021 at 15:29, Amit Saroha <dmarc-noreply_at_freelists.org> wrote:
Hi,
Regards,Amit S
Could you please elaborate on "c"? If a query contains multiple subqueries, are you saying the position of each one in the text of the query may influence the execution plan?
Thanks,Matt
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.
--
http://www.freelists.org/webpage/oracle-l
Received on Wed Jun 02 2021 - 18:12:37 CEST