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

 Very, very interesting -- and thank you! Nowadays (12c and later, for sure), I've been taught to the view that queries should only be hinted to give the CBO more information (so, a CARDINALITY hint or a DYNAMIC_SAMPLING hint).  Hints that tell the CBO _how_ to run the query (e.g., an INDEX hint) were kind of a last resort. If I don't use PUSH_SUBQ or ORDER_SUBQ, how does Oracle decide the other in which to run subqueries?  Is it always "subqueries after joins and then subqueries in order from top to bottom"?  Do statistics enter into it at all?  Is it moot because most subqueries are unnested anyway? Thanks again,Matt

    On Wednesday, June 2, 2021, 01:30:25 PM EDT, Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:  

 Matt,
Effects and options depend on version of Oracle (and whether CBQT has been disabled). By default Oracle used to execute subqueries at the last possible moment unless you added the push_subq hint to the code, in which case they were run as early as possible. There was also a question with multiple subqueries of the order of execution - originally I think Oracle ran them in the order they appeared in the text then. on one of the (10.2) upgrades, it switched to run them in reverse order. (Affected by a parameter setting).

Then on another upgrade the push_subq() hint was changed - instead of a single push_subq -- with no parameters - which "pushed" all the subqueries as much as possible you could push_subq(_at_qb_name) to push named subqueries individually and no_push_subq(_at_qb_name) to block pushing.  This was about the time that costing of subquery effects came into play.  https://jonathanlewis.wordpress.com/2007/03/09/push_subq/ Then in 12.2.0.1 Oracle introduced the order_subq() hint so you could specifiy the order in which subqueries should be applied - which I've written about fairly recently https://jonathanlewis.wordpress.com/2018/09/05/subquery-order/ RegardsJonathan Lewis

On Wed, 2 Jun 2021 at 17:12, mcpeakm_at_tempus-consulting-group.com <mcpeakm_at_tempus-consulting-group.com> wrote:

 Jonathan,
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 

    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,
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