Re: query rewrite
- From: kyle Hailey <kylelf@xxxxxxxxx>
- To: Toon Koppelaars <toon.koppelaars@xxxxxxxxxxx>
- Date: Tue, 29 Dec 2009 21:41:52 -0800
Thanks for the quick response.
The "SORT" lines in the EXPLAIN PLAN (this query and others) are unclear to
me. Often , as in this case (I think), the SORT in
SELECT STATEMENT
TABLE ACCESS - X
SORT
TABLE ACCESS - F
is really a nested loop with F driving the nested loop accesses into X, ie
for every row in F, look them up in table X.
Now if Oracle can do a NL access to the SELECT ON X, which is in the select
list, then theoretically it should be able to do a hash join.
Does the SELECT ON X have to be 'pushed' into the underlying FROM to be able
to perform a hash join? (or how about a merge join)
Best
Kyle
http://db-optimizer.blogspot.com/
Toon Koppelaars to me, ORACLE-L
show details 8:27 PM (1 hour ago)
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).
- Show quoted text -
--
Toon Koppelaars
RuleGen BV
Toon.Koppelaars@xxxxxxxxxxx
www.RuleGen.com <http://www.rulegen.com/>
TheHelsinkiDeclaration.blogspot.com<http://thehelsinkideclaration.blogspot.com/>
(co)Author: "Applied Mathematics for Database Professionals"
www.RuleGen.com/pls/apex/f?p=14265:13<http://www.rulegen.com/pls/apex/f?p=14265:13>
kyle Hailey to ORACLE-L
show details 8:00 PM (1 hour ago)
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:
