Results 1 to 14 of 14
  1. #1
    Join Date
    Nov 2003
    Posts
    7

    Unanswered: Urgent help reg sub-query

    Guys,
    Here is the problem!!!

    SELECT Fld1 FROM SCHEMA1.TABLE1
    WHERE
    SCHEMA1.TABLE1.Fld1= (SELECT Fld2 FROM Schema2.TABLE2)

    The two tables belong to diff schemas.
    Imagine the sub-query is retrieving 20 records of Fld2 and passing them to Fld1. It gives an error which says that the query cannot be executed since the sub-query is returning multiple no. of records.
    Can any one help me out.....
    Thanks,
    Shiva.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    not sure about the two different schemas, but you could try IN instead of =

    SELECT Fld1 FROM SCHEMA1.TABLE1
    WHERE
    SCHEMA1.TABLE1.Fld1
    IN (SELECT Fld2 FROM Schema2.TABLE2)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    Originally posted by r937
    not sure about the two different schemas, but you could try IN instead of =

    SELECT Fld1 FROM SCHEMA1.TABLE1
    WHERE
    SCHEMA1.TABLE1.Fld1
    IN (SELECT Fld2 FROM Schema2.TABLE2)
    I would always use an EXISTS clause for this kind of query. As far as I remember, using IN would cause a full table scan. I believe that this is the case for all DBMS.

    SELECT Fld1 FROM SCHEMA1.TABLE1
    WHERE
    EXISTS
    (SELECT * FROM Schema2.TABLE2
    WHERE SCHEMA1.TABLE1.Fld1 = Schema2.TABLE2.Fld2 )

    If I'm not talking rubbish, could somebody please explain why this is the case because it has bugged me for a long time now! Surely the optimiser should be able to rewrite an IN type query using the EXISTS syntax where the subset is the result of a SELECT?

    Damian

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    both the EXISTS and IN forms will be executed as a JOIN anyway

    again, assuming there's no difficulty operating across separate schemas

    SELECT Fld1 FROM SCHEMA1.TABLE1
    INNER JOIN Schema2
    ON SCHEMA1.TABLE1.Fld1 = Schema2.TABLE2.Fld2


    rudy

  5. #5
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    Originally posted by r937
    both the EXISTS and IN forms will be executed as a JOIN anyway

    Yes, but won't the IN form initially retrieve *all* the rows of its SELECT before performing the join?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no idea, but i doubt it

    you'd have to do the EXPLAIN to be sure

    (they have EXPLAIN in DB2, right?)

  7. #7
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    I know it seems ridiculous but I'm quite certain that this is the case.

    I've observed the optimiser taking a much more sensible path when I've re-written a query from the IN form to EXISTS.

    Aside from DB2, I've had limited exposure to Informix, Ingres and Oracle and I'm sure the same is true of them. I just don't understand why!

    Please put me out of my misery .

  8. #8
    Join Date
    Nov 2003
    Location
    Netherlands
    Posts
    96
    Originally posted by Damian Ibbotson
    I know it seems ridiculous but I'm quite certain that this is the case.

    I've observed the optimiser taking a much more sensible path when I've re-written a query from the IN form to EXISTS.

    Aside from DB2, I've had limited exposure to Informix, Ingres and Oracle and I'm sure the same is true of them. I just don't understand why!

    Please put me out of my misery .
    Hi,

    Using DB2 V8.1.4 on windows the IN and EXISTS queries show exactly the same explain path.

    Hope this helps.

  9. #9
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Originally posted by Damian Ibbotson
    I know it seems ridiculous but I'm quite certain that this is the case.

    I've observed the optimiser taking a much more sensible path when I've re-written a query from the IN form to EXISTS.

    Aside from DB2, I've had limited exposure to Informix, Ingres and Oracle and I'm sure the same is true of them. I just don't understand why!

    I think that the optimization outcome will depend very much on the statistics of the table in question. EXISTS predicate I believe is guaranteed to select a single record (since we don't care about other records if even a single one is found). In that case index access will always be chosen (provided that an appropriate index exists). On the other hand, IN requires that all matching records are retrieved, and the optimizer may choose a table scan even if an index exists, depending on the table size and distribution statistics (or lack thereof).

    If no index exists on the table then either predicate will cause a table scan; in that case the difference in the query execution time will be less significant, especially if the record that satisfies the condition is at the end of the table.

    Hope this makes sense.

    Nick
    ---
    "It does not work" is not a valid problem statement.

  10. #10
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    Originally posted by n_i

    EXISTS predicate I believe is guaranteed to select a single record (since we don't care about other records if even a single one is found). In that case index access will always be chosen (provided that an appropriate index exists). On the other hand, IN requires that all matching records are retrieved, and the optimizer may choose a table scan even if an index exists, depending on the table size and distribution statistics (or lack thereof).

    Hope this makes sense.
    That would make sense. It begs the question however, if I (as a human) am able to optimise such a query by applying some very trivial logic, why should the optimiser fail?

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    On the other hand, IN requires that all matching records are retrieved...
    is that true in DB2? if so, that's decidedly weird

    i'd've thought that as soon as one matching row is found, the IN is satisfied, and the optimizer wouldn't have to retrieve any more
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Originally posted by r937

    i'd've thought that as soon as one matching row is found, the IN is satisfied, and the optimizer wouldn't have to retrieve any more
    I think that's correct. However, before searching for a match the agent builds the entire result set of what's between the brackets of IN(...). At least this is what I think I remember after attending the DB2 optimization workshop :-)
    ---
    "It does not work" is not a valid problem statement.

  13. #13
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Originally posted by Damian Ibbotson
    That would make sense. It begs the question however, if I (as a human) am able to optimise such a query by applying some very trivial logic, why should the optimiser fail?
    I don't think it fails - it's just that its logic is probably different. You may want to try changing query optimization level to entice the optimizer to use more (or less) trivial logic :-)
    ---
    "It does not work" is not a valid problem statement.

  14. #14
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    Originally posted by r937
    is that true in DB2? if so, that's decidedly weird
    I think I eluded to it earlier in this thread; this behaviour is common in other DBMS (including Oracle). It probably makes perfect sense if you are proficient with relational algebra and set theory, which is why it makes no sense to me!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •