Re: query rewrite

They look the same to me, yes.

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@xxxxxxxxx> 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@xxxxxxxxxxx
www.RuleGen.com
TheHelsinkiDeclaration.blogspot.com

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

Other related posts: