query rewrite
- From: kyle Hailey <kylelf@xxxxxxxxx>
- To: ORACLE-L <Oracle-L@xxxxxxxxxxxxx>
- Date: Tue, 29 Dec 2009 20:00:51 -0800
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?
Other related posts:
