Re: query rewrite

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: