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