Re: query rewrite

As for your second question: I've never seen the CBO do transformations where subqueries in SELECT-clauses, are being 'pushed' into the underlying FROM-clause.

I guess it's just a kind of optimization that hasn't been 'programmed' (yet).

On Wed, Dec 30, 2009 at 5:00 AM, kyle Hailey <kylelf_at_gmail.com> wrote:

> Is there any difference between these two:
>
> select CASE WHEN F.f1 IS NULL
> THEN NULL
> ELSE (SELECT X.f2
> FROM X
> WHERE code_vl = F.f1)
> END AS f0
> from F;
>
> select CASE WHEN F.f1 IS NULL
> THEN NULL
> ELSE ( X.f2)
> END AS f0
> from F , X
> where code_vl(+) = F.f1;
>
> Here are the two plans I get respectively:
>
> SELECT STATEMENT
> TABLE ACCESS - X
> SORT
> TABLE ACCESS - F
>
> 30 seconds, 200,000 logical reads
>
> SELECT STATEMENT
> SORT
> HASH JOIN
> TABLE ACCESS - X
> TABLE ACCESS - F
>
> 1 second , 12,000 logical reads
>
>
> ?
> and if not, why does Oracle not seem to be able to do a hash join in the
> first case, but can fine in the second case?
>
>
>

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