Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Join Date
    Jul 2009
    Posts
    150

    Question Unanswered: What is better: join table or correlated subselect

    What is better: join table or correlated subselect ?

    Two scenarios with same result:

    Code:
    select t1.* from table1 t1 
    where t1.col1 = (select max(t2.col1)  from table1 t2
                              where  t2.col2 = t1.col2)
    or

    Code:
    select t1.* 
    from table1 t1 
    join table
    (select max(t2.col1)  Mcol1
    from table1 t2 where  t2.col2 = t1.col2) t3
    On t1.col1 = t3.Mcol1
    Same result. But where performance is better ?

    Kara S.

  2. #2
    Join Date
    Jul 2009
    Posts
    150

    Question

    Or maybe this one is better?

    Code:
    select t1.* 
    from table1 t1 
    join table
    (select max(t2.col1)  Mcol1, t1.col2
    from table1 t2 where  t2.col2 = t1.col2) t3
    On t1.col1 = t3.Mcol1
         and
         t1.col2 = t3.col2
    Kara S.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    unfortunately, both of your join examples are wrong, as they contain correlation

    try this --
    Code:
    SELECT table1.* 
      FROM ( SELECT col2
                  , MAX(col1) AS maxcol1
               FROM table1 
             GROUP
                 BY col2 ) AS m
    INNER
      JOIN table1 
        ON table1.col2 = m.col2
       AND table1.col1 = m.maxcol1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jul 2009
    Posts
    150

    Question

    Quote Originally Posted by r937
    unfortunately, both of your join examples are wrong, as they contain correlation

    try this --
    Code:
    SELECT table1.* 
      FROM ( SELECT col2
                  , MAX(col1) AS maxcol1
               FROM table1 
             GROUP
                 BY col2 ) AS m
    INNER
      JOIN table1 
        ON table1.col2 = m.col2
       AND table1.col1 = m.maxcol1
    How it could be ?
    I am using all of them in my everyday practice.

    Kara S.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What is better: join table or correlated subselect ?

    Two scenarios with same result:

    Same result. But where performance is better ?
    Kara,
    you can see and compare access plans by yourself using explain facility of DB2.

    DB2 choose same access path for both of your samples by my simple test on my DB2 9.7 for Windows environment.

    It may be different on other platform and/or other DB2 version/release.

  6. #6
    Join Date
    Jul 2009
    Posts
    150
    Quote Originally Posted by tonkuma
    Kara,
    you can see and compare access plans by yourself using explain facility of DB2.

    DB2 choose same access path for both of your samples by my simple test on my DB2 9.7 for Windows environment.

    It may be different on other platform and/or other DB2 version/release.
    That's true.

    But experts mostly make decisions better then DB2 Optimizer.

    I am interesting in your own expertise, not in explain solutions which are sometimes wrong.

    Kara S.

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If you want another query with possible different access plan,
    this would be worth to consider.
    (Result includes extra rank column.)
    Code:
    SELECT t1.*
      FROM (SELECT t1.*
                 , RANK()
                     OVER(PARTITION BY col2
                              ORDER BY col1 DESC) AS rank
              FROM table1 t1
           ) t1
     WHERE rank = 1
    ;

  8. #8
    Join Date
    Jul 2009
    Posts
    150
    Quote Originally Posted by tonkuma
    If you want another query with possible different access plan,
    this would be worth to consider.
    (Result includes extra rank column.)
    Code:
    SELECT t1.*
      FROM (SELECT t1.*
                 , RANK()
                     OVER(PARTITION BY col2
                              ORDER BY col1 DESC) AS rank
              FROM table1 t1
           ) t1
     WHERE rank = 1
    ;
    Thank you !
    This one looks nice, but I have z/os v8 and have no ability to fill how it's working... Where is my OLAP ?

    Kara S.

  9. #9
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by DB2Plus
    That's true.

    But experts mostly make decisions better then DB2 Optimizer.

    I am interesting in your own expertise, not in explain solutions which are sometimes wrong.

    Kara S.
    1. The DB2 optimizer will rewrite the query and choose the access path, not the "experts."

    2. The explain will show the actual access path that will be used when you actually run it, assuming the SQL statements are the same (and I don't understand why there would be a problem in explaining the same statement that you intend to run).

    This is not Oracle we are talking about. The DB2 optimizer is a very sophisticated piece of software.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If you want to get useful advice or variable answer in the forum, you shoud write your platform and DB2 version/release.

    At least, at the moment I wrote my environment, you should write your environment.
    DB2 choose same access path for both of your samples by my simple test on my DB2 9.7 for Windows environment.

    It may be different on other platform and/or other DB2 version/release.
    I wrote about OLAP support on z/OS in my last post in the thread http://www.dbforums.com/db2/1648669-help-query.html, like this:
    DB2 for z/OS Version 8 does not support ORDER BY and FETCH FIRST n ROWs ONLY clause in a subselect nor OLAP functions.
    They are supported on DB2 for z/OS Version 9.
    Anyway, here is another query:
    Code:
    SELECT t1.*
      FROM table1 t1 
     WHERE t1.col1 
           >= ALL(SELECT t2.col1
                    FROM table1 t2
                   WHERE t2.col2 = t1.col2)
    ;
    Last edited by tonkuma; 10-11-09 at 17:34.

  11. #11
    Join Date
    Jul 2009
    Posts
    150

    Thumbs up

    Quote Originally Posted by tonkuma
    If you want to get useful advice or variable answer in the forum, you shoud write your platform and DB2 version/release.

    At least, at the moment I wrote my environment, you should write your environment.


    I wrote about OLAP support on z/OS in my last post in the thread http://www.dbforums.com/db2/1648669-help-query.html, like this:


    Anyway, here is another query:
    Code:
    SELECT t1.*
      FROM table1 t1 
     WHERE t1.col1 
           >= ALL(SELECT t2.col1
                    FROM table1 t2
                   WHERE t2.col2 = t1.col2)
    ;
    You have to know, tonkuma, I appreciate your job !
    I am interesting about different platforms....
    But I can't test your statement, only.

    That's because I am working for the biggest financial company in the world, and then bigger company then more conservative.

    The last one also looks good for me, but maybe you can explain how RANK can improve performance ?

    Sincerely yours, Kara.

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by tonkuma
    Anyway, here is another query:
    Code:
    SELECT t1.*
      FROM table1 t1 
     WHERE t1.col1 
           >= ALL(SELECT t2.col1
                    FROM table1 t2
                   WHERE t2.col2 = t1.col2)
    ;
    that's pretty subtle but i like it

    here are several more solutions to the same pattern: ~jk groupwise max
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I am interesting about different platforms....
    But I can't test your statement, only.

    The last one also looks good for me, but maybe you can explain how RANK can improve performance ?
    If you are interesting about different platforms than z/OS,
    you can install DB2 Express-C(Download: IBM DB2 Express-C 9.7) on your Windows or Linux machine and get explain of queries by yourself.

    You will see DB2 choose quite different access path for OLAP version than join table or correlated subselect.

  14. #14
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I'm sorry.
    This was double posted. I'll erase this.

  15. #15
    Join Date
    Jul 2009
    Posts
    150

    Thumbs up

    Quote Originally Posted by r937
    that's pretty subtle but i like it

    here are several more solutions to the same pattern: ~jk groupwise max
    Oh, I see Canada has the strong DB2 team.

    You, n_i, db2girl.... Who else ?

    What do you have in you pocket ? Please, show to us.

    Kara S.

Posting Permissions

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