Re: query rewrite
- From: kyle Hailey <kylelf@xxxxxxxxx>
- To: Toon Koppelaars <toon.koppelaars@xxxxxxxxxxx>
- Date: Tue, 29 Dec 2009 22:19:24 -0800
one difference -
the first query will break if the correlated sub query returns more than one
value where as the second query will return the mulitple rows.
On Tue, Dec 29, 2009 at 9:41 PM, kyle Hailey <kylelf@xxxxxxxxx> wrote:
>
> 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:
