Re: query rewrite

From: Toon Koppelaars <toon.koppelaars_at_rulegen.com>
Date: Wed, 30 Dec 2009 18:53:20 +0100
Message-ID: <ecf3dae70912300953l17b9d788wc60166000307c26c_at_mail.gmail.com>

Ahh yes, you are absolutely right. I missed that.

The CBO could only rewrite it, if it knew that code_vl is a (declared) key in table X. Then and only then would the two queries be semantically equivalent.

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/
>
>

-- 
Toon Koppelaars
RuleGen BV
Toon.Koppelaars_at_RuleGen.com
www.RuleGen.com
TheHelsinkiDeclaration.blogspot.com

(co)Author: "Applied Mathematics for Database Professionals"
www.RuleGen.com/pls/apex/f?p=14265:13

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

Received on Wed Dec 30 2009 - 11:53:20 CST