Re: query rewrite
- From: Toon Koppelaars <toon.koppelaars@xxxxxxxxxxx>
- To: kylelf@xxxxxxxxx
- Date: Wed, 30 Dec 2009 05:27:25 +0100
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:
