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

  • From: Matt McPeak <mcpeakm@xxxxxxxxxxxxxxxxxxxxxxxxxxx>
  • To: "Oracle L." <oracle-l@xxxxxxxxxxxxx>, Jonathan Lewis <jlewisoracle@xxxxxxxxx>
  • Date: Wed, 2 Jun 2021 17:55:55 +0000 (UTC)

 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@xxxxxxxxx> wrote:  
Effects and options depend on version of Oracle (and whether CBQT has been 
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(@qb_name) to push named subqueries individually 
and no_push_subq(@qb_name) to block pushing.  This was about the time that 
costing of subquery effects came into play.
Then in 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
RegardsJonathan Lewis

On Wed, 2 Jun 2021 at 17:12, mcpeakm@xxxxxxxxxxxxxxxxxxxxxxxxxxx 
<mcpeakm@xxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

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?
    On Thursday, May 27, 2021, 05:05:55 AM EDT, Jonathan Lewis 
<jlewisoracle@xxxxxxxxx> 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@xxxxxxxxxxxxx> wrote:

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 


Other related posts: