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@xxxxxxxxx>wrote: > > On Wed, Dec 30, 2009 at 6:19 AM, kyle Hailey <kylelf@xxxxxxxxx> 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@xxxxxxxxxxx www.RuleGen.com TheHelsinkiDeclaration.blogspot.com (co)Author: "Applied Mathematics for Database Professionals" www.RuleGen.com/pls/apex/f?p=14265:13