Re: query rewrite

From: Ghassan Salem <salem.ghassan_at_gmail.com>
Date: Wed, 30 Dec 2009 09:53:43 +0100
Message-ID: <411d50f60912300053hd98aa42s79d7d3f59b388856_at_mail.gmail.com>

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_at_gmail.com>wrote:

>
> On Wed, Dec 30, 2009 at 6:19 AM, kyle Hailey <kylelf_at_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/
>
>

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

Received on Wed Dec 30 2009 - 02:53:43 CST