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?