Results 1 to 14 of 14
  1. #1
    Join Date
    Jan 2003
    Posts
    47

    Unanswered: Does Inner Query pulls All the records?

    I am having the following sql

    update tableB
    set columnb='a'
    where columna in(select columna from tableA)

    tableA contains 2 Million rows and tableB contains 2000 rows. By executing the sql does the inner query fetches all 2 Million rows? Is there any way I can verify?

    Thanks
    Shri
    p.srinivasarao

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Yes it does.

    You may want this:

    update tableB B
    set columnb= 'a'
    where columna in
    (select columna from tableA A where A. columna = B.columna)

    or maybe this:

    update tableB
    set columnb= 'a'
    where columna in (select columna from tableA fetch first 1 rows only)

    or maybe this (but it will read all rows in tableA):

    update tableB
    set columnb= 'a'
    where columna in (select distinct(columna) from tableA)
    Last edited by Marcus_A; 10-14-05 at 04:00.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jan 2003
    Posts
    47
    Thanks Marcus
    p.srinivasarao

  4. #4
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by Marcus_A
    update tableB
    set columnb= 'a'
    where columna in (select distinct(columna) from tableA)
    This is logically equivalent to the original query, but will in general be less performant.
    (Correct me if I'm wrong.)
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  5. #5
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    If tableA contains an index on columna, most likely the inner query will use an index-only lookup; i.e., the table will not be scanned at all.
    You may verify this by using EXPLAIN.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by Peter.Vanroose
    If tableA contains an index on columna, most likely the inner query will use an index-only lookup; i.e., the table will not be scanned at all.
    You may verify this by using EXPLAIN.
    The original query makes little sense without the DISTINCT clause, and even then it may not be what was intended. A DB2 query with a very long "IN" list will not perform well, and in the past there have been limits on how may values can there can be in such a clause.
    Last edited by Marcus_A; 10-15-05 at 11:44.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    I would think a good optimizer would be able to find out by itself whether it reorganizes a long "IN" list or not ...

    Anybody did some experiments regarding performance of long IN lists?
    Any differences between DB2 7 and 8 in this respect?
    Last edited by Peter.Vanroose; 10-15-05 at 16:50.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  8. #8
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I would write
    Code:
    update tableB B
    set columnb= 'a'
    where EXISTS 
    (select 1 from tableA A where A.columna = B.columna)
    Would the performance be better or worse than using the IN clause ?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  9. #9
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by Wim
    update tableB B
    set columnb= 'a'
    where EXISTS
    (select 1 from tableA A where A.columna = B.columna)
    As compared to the logically equivalent
    Code:
    update tableB
    set columnb= 'a'
    where columna IN (select columna from tableA)
    the "EXISTS" solution requires running the subquery for every of the 1000 rows of the outer tableA, but on the other hand these 1000 subquery runs each return just 1 value (true or false), while the single subquery run in the "IN" solution may return 1000000 values.
    Unless the optimizer is clever enough, I would vote for the "EXISTS" query as being the fastest one. (Roughly 1000 times faster?? Anybody did the experiment?)
    All will of course also depend on the presence of indices.
    Last edited by Peter.Vanroose; 10-15-05 at 17:59.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  10. #10
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    The problem is that the second query is not functionally the same as the first. The second query says that any value in the tableA is valid for any row in tableB, whereas the first query correlates the query to the equivlent row in tableA which have the same value for columna (WHERE A.columna = B.columna).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  11. #11
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    The two queries certainly are equivalent!
    The first one will change those rows of TableB for which that row's columna value exists in columna of TableA, while the second one will change those rows of TableB for which that row's columna is in the list of columna values of TableA ...
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  12. #12
    Join Date
    Oct 2001
    Posts
    46
    Quote Originally Posted by Peter.Vanroose
    The two queries certainly are equivalent!
    The first one will change those rows of TableB for which that row's columna value exists in columna of TableA, while the second one will change those rows of TableB for which that row's columna is in the list of columna values of TableA ...
    Isn't that like saying "take two steps forward, one step back" is logically equivalent to walking one step at a time? Afterall, the net gain is one step forward!

  13. #13
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by test
    Isn't that like saying "take two steps forward, one step back" is logically equivalent to walking one step at a time? Afterall, the net gain is one step forward!
    In this case, not necessarily:
    The "IN" version might by better when the inner table (A) is small and the outer table (B) is large, while the "EXISTS" version will be likely to be more performant in the opposite situation.

    So it is more like saying "which is better: two small steps forward, or one big step?" where the answer depends on whether you are climbing a mountain trail or not.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  14. #14
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You are probably right that the two are equivilent. It is difficult to understand what the business purpose of the queries are because they use completely fake table names and columns. It would be easier to understand if the DB2 Sample database was used to make up such queries.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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