What is better: join table or correlated subselect

DB2Plus · #1 (**permalink**) 10-11-09, 12:19

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.

DB2Plus · #2 (**permalink**) 10-11-09, 12:43

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.

r937 · #3 (**permalink**) 10-11-09, 13:16

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

DB2Plus · #4 (**permalink**) 10-11-09, 13:27

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.

tonkuma · #5 (**permalink**) 10-11-09, 13:42

Quote:

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.

DB2Plus · #6 (**permalink**) 10-11-09, 13:50

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.

tonkuma · #7 (**permalink**) 10-11-09, 14:47

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
;

DB2Plus · #8 (**permalink**) 10-11-09, 15:31

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.

Marcus_A · #9 (**permalink**) 10-11-09, 16:18

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.

tonkuma · #10 (**permalink**) 10-11-09, 16:26

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.

Quote:

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 Help with Query, like this:

Quote:

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)
;

DB2Plus · #11 (**permalink**) 10-11-09, 16:50

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 Help with Query, 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.

r937 · #12 (**permalink**) 10-11-09, 17:07

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

tonkuma · #13 (**permalink**) 10-11-09, 17:17

Quote:

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.