Re: query rewrite

From: Stephane Faroult <sfaroult_at_roughsea.com>
Date: Wed, 30 Dec 2009 20:47:28 +0100
Message-ID: <4B3BAE50.4030502_at_roughsea.com>

I missed it too on first reading :-). In fact, seeing the select list subquery first it was natural to assume a 1-1 relationship afterwards. I fully agree on the fact that the two queries would be semantically equivalent if code_vl were declared unique in table X, but to answer Kyle's question more precisely, I don't think that it would be enough for the optimizer to rewrite the query. For me, the stumbling block is the case ... end construct. In one case, you start with a single-table query, and the condition on one column from this table triggers a conditional second query. With the outer join, the starting point is a different relation. With the second query, you could imagine in the case statement an additional WHEN clause that tests columns that are issued from table X - something that would be impossible with the first writing. Perhaps that an optimizer would manage to "get" the equivalence with the other form for case - using a case nvl(f.f1, 'something improbable') when ....
 But I doubt it with the function.

In other words, the fact that the two queries are semantically equivalent, even with properly defined constraints, depends on one particular "configuration" of the case statement. The path of less resistance is not trying to rewrite it cleverly ... after all, developers are expected to have some ideas about SQL ...

SF

Toon Koppelaars wrote:
> Ahh yes, you are absolutely right. I missed that.
>
> The CBO could only rewrite it, if it knew that code_vl is a (declared)
> key in table X. Then and only then would the two queries be
> semantically equivalent.
>
>
> On Wed, Dec 30, 2009 at 9:49 AM, Marcin Przepiorowski
> <pioro1_at_gmail.com <mailto:pioro1_at_gmail.com>> wrote:
>
>
> On Wed, Dec 30, 2009 at 6:19 AM, kyle Hailey <kylelf_at_gmail.com
> <mailto:kylelf_at_gmail.com>> wrote:
>
>
> 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.
>
>
> Hi Kyle,
>
> I have spend last 2 weeks working with query as in 1 example
> and subquery never has been pushed into FROM section.
> My general execution plan in 10.1.0.4 looks like this
>
> SELECT STATEMENT
> TABLE ACCESS - X - or join of more then one table
> SORT
> TABLE ACCESS - F - or join of more then one table
>
> or
>
> SELECT STATEMENT
> TABLE ACCESS - X - or join of more then one table
> FILTER
> TABLE ACCESS - F - or join of more then one table
>
>
> regards,
> Marcin Przepiorowski
> http://oracleprof.blogspot.com/
>
>
>
>
>
> --
> Toon Koppelaars
> RuleGen BV
> Toon.Koppelaars_at_RuleGen.com
> 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>
>

-- 
Stephane Faroult
RoughSea Ltd <http://www.roughsea.com>


--
http://www.freelists.org/webpage/oracle-l

Received on Wed Dec 30 2009 - 13:47:28 CST