MIME-Version: 1.0
References: <258487026.168190.1622039346948.ref@mail.yahoo.com>
 <258487026.168190.1622039346948@mail.yahoo.com> <CAGtsp8miSA0+WON=vppLcEYt8eZUaezYwn-O6bhLGVKOv9u3eQ@mail.gmail.com>
 <1971169072.1512775.1622650357675@mail.yahoo.com>
In-Reply-To: <1971169072.1512775.1622650357675@mail.yahoo.com>
From: Jonathan Lewis <jlewisoracle@gmail.com>
Date: Wed, 2 Jun 2021 18:30:09 +0100
Message-ID: <CAGtsp8=D3Anfj8yFPjpxQi85dnAngVwTDT9yLuMKn8qV-DVr=g@mail.gmail.com>
Subject: Re: Replace Not Exist with Left Outer Join and Is Null
To: "Oracle L." <oracle-l@freelists.org>
Content-Type: multipart/alternative; boundary="00000000000023377905c3cbd0ec"
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-To: jlewisoracle@gmail.com
--00000000000023377905c3cbd0ec
Content-Type: text/plain; charset="UTF-8"

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(@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.
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@tempus-consulting-group.com <
mcpeakm@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@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@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
>
>
>

--00000000000023377905c3cbd0ec
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>Matt,</div><div><br></div><div>Effects and options de=
pend on version of Oracle (and whether CBQT has been disabled).</div><div><=
br></div><div>By default Oracle used to execute subqueries at the last poss=
ible moment unless you added the push_subq hint to the code, in which case =
they were run as early as possible.</div><div><br></div><div>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 on=
e of the (10.2) upgrades, it switched to run them in reverse order. (Affect=
ed by a parameter setting).<br></div><div><br></div><div>Then on another up=
grade the push_subq() hint was changed - instead of a single push_subq -- w=
ith no parameters - which &quot;pushed&quot; all the subqueries as much as =
possible you could push_subq(@qb_name) to push named subqueries individuall=
y and no_push_subq(@qb_name) to block pushing.=C2=A0 This was about the tim=
e that costing of subquery effects came into play.=C2=A0 <a href=3D"https:/=
/jonathanlewis.wordpress.com/2007/03/09/push_subq/">https://jonathanlewis.w=
ordpress.com/2007/03/09/push_subq/</a></div><div><br></div><div>Then in 12.=
2.0.1 Oracle introduced the order_subq() hint so you could specifiy the ord=
er in which subqueries should be applied - which I&#39;ve written about fai=
rly recently <a href=3D"https://jonathanlewis.wordpress.com/2018/09/05/subq=
uery-order/">https://jonathanlewis.wordpress.com/2018/09/05/subquery-order/=
</a></div><div><br></div><div>Regards</div><div>Jonathan Lewis</div><div><b=
r></div><div><br></div><div><br></div><div><br></div><div><br></div></div><=
br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On Wed,=
 2 Jun 2021 at 17:12, <a href=3D"mailto:mcpeakm@tempus-consulting-group.com=
">mcpeakm@tempus-consulting-group.com</a> &lt;<a href=3D"mailto:mcpeakm@tem=
pus-consulting-group.com">mcpeakm@tempus-consulting-group.com</a>&gt; wrote=
:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.=
8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div><div styl=
e=3D"font-family:courier new,courier,monaco,monospace,sans-serif;font-size:=
16px"><div></div>
        <div dir=3D"ltr">Jonathan,</div><div dir=3D"ltr"><br></div><div dir=
=3D"ltr">Could you please elaborate on &quot;c&quot;?=C2=A0 If a query cont=
ains multiple subqueries, are you saying the position of each one in the te=
xt of the query may influence the execution plan?</div><div dir=3D"ltr"><br=
></div><div dir=3D"ltr">Thanks,</div><div dir=3D"ltr">Matt</div><div dir=3D=
"ltr">=C2=A0</div><div><br></div>
       =20
        </div><div id=3D"gmail-m_8252260889571931729ydpd3f3efe5yahoo_quoted=
_3120688161">
            <div style=3D"font-family:&quot;Helvetica Neue&quot;,Helvetica,=
Arial,sans-serif;font-size:13px;color:rgb(38,40,42)">
               =20
                <div>
                    On Thursday, May 27, 2021, 05:05:55 AM EDT, Jonathan Le=
wis &lt;<a href=3D"mailto:jlewisoracle@gmail.com" target=3D"_blank">jlewiso=
racle@gmail.com</a>&gt; wrote:
                </div>
                <div><br></div>
                <div><br></div>
                <div><div id=3D"gmail-m_8252260889571931729ydpd3f3efe5yiv91=
51160359"><div><div dir=3D"ltr"><div dir=3D"ltr"><div><br clear=3D"none"></=
div><div>Before trying a rewrite - or going though the painful process of t=
rying to interpret a CBO trace file, you could pursue three ideas:</div><di=
v>a) Does Oracle consider subquery unnesting to be illegal ... try adding a=
n /*+ unnest */ hint to the subquery itself to see if it is possible (even =
it produces a very bad plan as a result)</div><div>b) Does Oracle think the=
 subquery will happen only a very small number of times - if so address add=
ress the cause of of the bad cardinality estimate before chasing the subque=
ry error</div><div>c) Has Oracle run the subquery at the earliest possible =
moment or the latest possible moment - if there are multiple subqueries hav=
e they been run in the wrong order: would it help to change the timing of w=
hen the subquery runs.</div><div><br clear=3D"none"></div><div>Regards</div=
><div>Jonathan Lewis</div><div><br clear=3D"none"></div><div><br clear=3D"n=
one"></div><div>

</div></div><br clear=3D"none"><div id=3D"gmail-m_8252260889571931729ydpd3f=
3efe5yiv9151160359yqt83461"><div><div dir=3D"ltr">On Wed, 26 May 2021 at 15=
:29, Amit Saroha &lt;<a shape=3D"rect" href=3D"mailto:dmarc-noreply@freelis=
ts.org" rel=3D"nofollow" target=3D"_blank">dmarc-noreply@freelists.org</a>&=
gt; wrote:<br clear=3D"none"></div><blockquote style=3D"margin:0px 0px 0px =
0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div><div st=
yle=3D"font-family:Helvetica Neue,Helvetica,Arial,sans-serif;font-size:16px=
"><div dir=3D"ltr">Hi,</div><div dir=3D"ltr"><br clear=3D"none"></div><div =
dir=3D"ltr">In one of the queries TKProof shows, most time is spent in acce=
ssing a table thousands of times inside NOT EXIST condition.</div><div dir=
=3D"ltr"><br clear=3D"none"></div><div dir=3D"ltr">I am looking for your in=
puts if it&#39;s a promising idea to replace NOT EXIST with the LEFT OUTER =
JOIN and IS NULL condition?</div><div dir=3D"ltr"><br clear=3D"none"></div>=
<div dir=3D"ltr">Any inputs in this regard are appreciated.</div><div dir=
=3D"ltr"><br clear=3D"none"></div><div dir=3D"ltr"><br clear=3D"none"></div=
><div dir=3D"ltr">Regards,</div><div dir=3D"ltr">Amit S=C2=A0</div><div dir=
=3D"ltr"><br clear=3D"none"></div><div dir=3D"ltr"><br clear=3D"none"></div=
></div></div></blockquote></div></div></div>
</div></div></div>
            </div>
        </div></div></blockquote></div>

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