FAQ
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).
On Wed, Dec 30, 2009 at 5:00 AM, kyle Hailey wrote:

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?

--
Toon Koppelaars
RuleGen BV
Toon.Koppelaars_at_RuleGen.com
www.RuleGen.com
TheHelsinkiDeclaration.blogspot.com

(co)Author: "Applied Mathematics for Database Professionals"
www.RuleGen.com/pls/apex/f?p=14265:13

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

Search Discussions

  • kyle Hailey at Dec 30, 2009 at 6:19 am
    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 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_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>
    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?
    --
    http://www.freelists.org/webpage/oracle-l
  • Marcin Przepiorowski at Dec 30, 2009 at 8:49 am

    On Wed, Dec 30, 2009 at 6:19 AM, kyle Hailey 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/
  • Ghassan Salem at Dec 30, 2009 at 8:53 am
    Well, semantically, it cannot be pushed, as Kyle noted, if in the first
    case, the select returns more than one row, it gives an error, in the second
    case, it gives more than one row, so, semantically, the queries are not

    equivalent, and CBO cannot rewrite them. In all cases, I don't think it even
    tries to do so.

    rgds
    On Wed, Dec 30, 2009 at 9:49 AM, Marcin Przepiorowski wrote:

    On Wed, Dec 30, 2009 at 6:19 AM, kyle Hailey 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/
    --
    http://www.freelists.org/webpage/oracle-l
  • Toon Koppelaars at Dec 30, 2009 at 5:53 pm
    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 wrote:

    On Wed, Dec 30, 2009 at 6:19 AM, kyle Hailey 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
    TheHelsinkiDeclaration.blogspot.com

    (co)Author: "Applied Mathematics for Database Professionals"
    www.RuleGen.com/pls/apex/f?p=14265:13

    --
    http://www.freelists.org/webpage/oracle-l
  • Stephane Faroult at Dec 30, 2009 at 7:47 pm
    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
    wrote:

    On Wed, Dec 30, 2009 at 6:19 AM, kyle Hailey > 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
  • kyle Hailey at Dec 30, 2009 at 3:33 pm
    Would you say that these correlated sub queries in the select is are a BAD
    idea since no one has seen Oracle trying to push them into the FROM clause
    thus blocking efficient execution paths?

    Best
    Kyle Hailey
    http://db-optimizer.blogspot.com/
    <http://db-optimizer.blogspot.com/>
    On Wed, Dec 30, 2009 at 1:41 AM, Marcin Przepiorowski wrote:

    On Wed, Dec 30, 2009 at 8:53 AM, Ghassan Salem
    wrote:
    Well, semantically, it cannot be pushed, as Kyle noted, if in the first
    case, the select returns more than one row, it gives an error, in the second
    case, it gives more than one row, so, semantically, the queries are not
    equivalent, and CBO cannot rewrite them. In all cases, I don't think it even
    tries to do so.
    You are right but I have seen a lot of "workarounds" for a bad written
    subqueries with
    min or max taken from ID or name just to achieve a single row return.

    regards,
    Marcin Przepiorowski
    --
    http://www.freelists.org/webpage/oracle-l
  • Marcin Przepiorowski at Dec 30, 2009 at 3:52 pm

    On Wed, Dec 30, 2009 at 3:33 PM, kyle Hailey wrote:

    You are right but I have seen a lot of "workarounds" for a bad written
    subqueries with
    min or max taken from ID or name just to achieve a single row return.
    Would you say that these correlated sub queries in the select is are a BAD
    idea since no one has seen Oracle trying to push them into the FROM clause
    thus blocking efficient execution paths?
    Kyle,

    Not really - my main point was that subquery in select has to return one row
    and for some people it is very difficult to find a proper join and
    filter condition
    and some of them are using min or max function to limit number of rows to one.
    In that case query will be always working fine.

    Other problem is that these kind of query are sometimes very expensive
    from resource point of view
    and sometimes a simple rewrite or pushup a subquery to join section can help.

    regards,
    Marcin Przepiorowski
  • Ghassan Salem at Dec 30, 2009 at 5:43 pm
    Kyle,
    for one, I haven't seen CBO trying to optimize them in the way you're
    asking.
    I don't think that it will do such a thing in the near future, as it is not
    easy to insure that the result will be consistent (i.e. be sure that the
    query always returns one row, and that the conversion to a join will not
    introduce wrong results, e.g. due to nulls handling, .....) Well at least
    that is how I see it, but CBO guys sure know better than me if it is
    feasible or not.

    rgds
    On Wed, Dec 30, 2009 at 4:33 PM, kyle Hailey wrote:


    Would you say that these correlated sub queries in the select is are a BAD
    idea since no one has seen Oracle trying to push them into the FROM clause
    thus blocking efficient execution paths?

    Best
    Kyle Hailey
    http://db-optimizer.blogspot.com/
    <http://db-optimizer.blogspot.com/>
    On Wed, Dec 30, 2009 at 1:41 AM, Marcin Przepiorowski wrote:

    On Wed, Dec 30, 2009 at 8:53 AM, Ghassan Salem
    wrote:
    Well, semantically, it cannot be pushed, as Kyle noted, if in the first
    case, the select returns more than one row, it gives an error, in the second
    case, it gives more than one row, so, semantically, the queries are not
    equivalent, and CBO cannot rewrite them. In all cases, I don't think it even
    tries to do so.
    You are right but I have seen a lot of "workarounds" for a bad written
    subqueries with
    min or max taken from ID or name just to achieve a single row return.

    regards,
    Marcin Przepiorowski
    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedDec 30, '09 at 4:27a
activeDec 30, '09 at 7:47p
posts9
users5
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase