DomainKey-Signature: a=rsa-sha1; c=nofws;        d=gmail.com; s=gamma;        h=mime-version:in-reply-to:references:date:message-id:subject:from:to         :cc:content-type;        b=gw/0EQx/17tHBFhj+vwFXm8uDQmhQWqM2/5/R/6sTz+iy9iEO2dARqozS8Pb+FtU66         4zNQJRQBjND99uIcNqe9uia8hul5vd9eKtAZEbtmX43r8OqevN0vVcJpRCYS4UMuMCjr         kvtvN+/R5N8S9YbX1URhCP0Dc8DIahfqvF3EA=
MIME-Version: 1.0
In-Reply-To: <3f5690af0912300049x48b86386ude32e73797608e90@mail.gmail.com>
References: <6f373fd20912292000we5e8fe1lf1483558f82ad933@mail.gmail.com>	 <ecf3dae70912292027j4008d75ct80fd0d8652e5a6b9@mail.gmail.com>	 <6f373fd20912292141s4aeaad94pcf06c4d0a4fd8948@mail.gmail.com>	 <6f373fd20912292219v44221d3apa970006ac8435ae5@mail.gmail.com>	 <3f5690af0912300049x48b86386ude32e73797608e90@mail.gmail.com>
Date: Wed, 30 Dec 2009 09:53:43 +0100
Message-ID: <411d50f60912300053hd98aa42s79d7d3f59b388856@mail.gmail.com>
Subject: Re: query rewrite
From: Ghassan Salem <salem.ghassan@gmail.com>
To: pioro1@gmail.com
Cc: kylelf@gmail.com, Toon Koppelaars <toon.koppelaars@rulegen.com>, 	ORACLE-L <Oracle-L@freelists.org>
Content-Type: multipart/alternative; boundary=0016e6d7856d961da3047bee4668
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-to: salem.ghassan@gmail.com
--0016e6d7856d961da3047bee4668
Content-Type: text/plain; charset=ISO-8859-1

Well, semantically, it cannot be pushed, as Kyle noted, if in the first
case, the select returns more than one row, it gives an error, in the second
case, it gives more than one row, so, semantically, the queries are not
equivalent, and CBO cannot rewrite them. In all cases, I don't think it even
tries to do so.

rgds

On Wed, Dec 30, 2009 at 9:49 AM, Marcin Przepiorowski <pioro1@gmail.com>wrote:

>
> On Wed, Dec 30, 2009 at 6:19 AM, kyle Hailey <kylelf@gmail.com> wrote:
>
>>
>> one difference -
>> the first query will break if the correlated sub query returns more than
>> one value where as the second query will return the mulitple rows.
>>
>>
> Hi Kyle,
>
> I have spend last 2 weeks working with query as in 1 example
> and subquery never has been pushed into FROM section.
> My general execution plan in 10.1.0.4 looks like this
>
> SELECT STATEMENT
>    TABLE ACCESS - X - or join of more then one table
>    SORT
>       TABLE ACCESS - F - or join of more then one table
>
> or
>
> SELECT STATEMENT
>    TABLE ACCESS - X - or join of more then one table
>     FILTER
>       TABLE ACCESS - F - or join of more then one table
>
>
> regards,
> Marcin Przepiorowski
> http://oracleprof.blogspot.com/
>
>

--0016e6d7856d961da3047bee4668
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Well, semantically, it cannot be pushed, as Kyle noted, if in the first cas=
e, the select returns more than one row, it gives an error, in the second c=
ase, it gives more than one row, so, semantically, the queries are not equi=
valent, and CBO cannot rewrite them. In all cases, I don&#39;t think it eve=
n tries to do so.<br>
<br>rgds<br><br><div class=3D"gmail_quote">On Wed, Dec 30, 2009 at 9:49 AM,=
 Marcin Przepiorowski <span dir=3D"ltr">&lt;<a href=3D"mailto:pioro1@gmail.=
com">pioro1@gmail.com</a>&gt;</span> wrote:<br><blockquote class=3D"gmail_q=
uote" style=3D"border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0=
pt 0.8ex; padding-left: 1ex;">
<div class=3D"im"><br><div class=3D"gmail_quote">On Wed, Dec 30, 2009 at 6:=
19 AM, kyle Hailey <span dir=3D"ltr">&lt;<a href=3D"mailto:kylelf@gmail.com=
" target=3D"_blank">kylelf@gmail.com</a>&gt;</span> wrote:<br><blockquote c=
lass=3D"gmail_quote" style=3D"border-left: 1px solid rgb(204, 204, 204); ma=
rgin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">

<div><br></div>one difference -<div>the first query will break if the=A0cor=
related=A0sub query=A0returns more than one value where as the second query=
 will return the mulitple rows.</div><div><div></div><div><div><br>
</div></div></div></blockquote></div><br></div>Hi Kyle,<br><br>I have spend=
 last 2 weeks working with query as in 1 example<br>and subquery never has =
been pushed into FROM section.<br>My general execution plan in 10.1.0.4 loo=
ks like this <br>

<br><div>SELECT STATEMENT</div><div>=A0=A0 TABLE ACCESS - X - or join of mo=
re then one table<br></div><div>=A0=A0 SORT</div><div>=A0=A0 =A0 =A0TABLE A=
CCESS - F - or join of more then one table<br><br>or<br><br><div>SELECT STA=
TEMENT</div>


<div>=A0=A0 TABLE ACCESS - X - or join of more then one table<br>
</div>
<div>=A0=A0 FILTER<br></div>
<div>=A0=A0 =A0 =A0TABLE ACCESS - F - or join of more then one table<br>
</div><br></div><br>regards,<br><font color=3D"#888888">Marcin Przepiorowsk=
i<br><a href=3D"http://oracleprof.blogspot.com/" target=3D"_blank">http://o=
racleprof.blogspot.com/</a><br>=A0<br>
</font></blockquote></div><br>

--0016e6d7856d961da3047bee4668--
--
http://www.freelists.org/webpage/oracle-l