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

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/

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