Issue on query for joining same table.

dharmaraj_ganesan · #1 (**permalink**) 05-25-10, 10:49

Hello ppl,

I am on DB2 V8.2 in AIX 5.3.

I am not gud at query tuning, i am in need of help here.

I have one query taking long time to complete,

select tab1.x,tab1.y from tab1 where tab1.a=(select max(tab1alias.a) from tab1 tab1alias where tab1alias.b= tab1.b and tab1alias.c=tab1.c and tab1alias.d =tab1.d) and tab1.e= const1 and tab2.f=const2

The problem is with the red highlighted part. Tats wat i came to know from the explain plan.

Is there any way to rewrite tat part into a different form so that the query runs in quick time or any indexe will help?

Index advisor didnt gave any recommendations.

Note: a,b,c and d are unique columns on that table.

tonkuma · #2 (**permalink**) 05-25-10, 12:16

Quote:

Note: a,b,c and d are unique columns on that table.

CASE1:
If that means each of a,b,c and d are unique
and not "the conbination of a,b,c and d is unique",
then I thought that the query would be rewitten as following...
(I like to use upper case for keywords and to format queries by using newlines and indentions.)

Code:

SELECT t1.x , t1.y
  FROM tab1 t1
 WHERE
 /*
       t1.a =
       (SELECT MAX(t1a.a)
          FROM tab1 t1a
         WHERE t1a.b = t1.b
           AND t1a.c = t1.c
           AND t1a.d = t1.d
       )
   AND
 */
       t1.e = const1
   AND t1.f = const2

dharmaraj_ganesan · #3 (**permalink**) 05-25-10, 13:05

no buddy, combination of a,b,c and d are unique.

unique index is created on these 4 columns.

ARWinner · #4 (**permalink**) 05-25-10, 14:06

My guess is that your unique index is on a,b,c,d. If it was on b,c,d,a then the query should use this index. If you can drop the old index and create it in the new order, that should fix your problem. If you cannot drop the existing index, then a new one could be added, but it would essentially be a redundant index.

Andy

dharmaraj_ganesan · #5 (**permalink**) 05-25-10, 14:22

Index is on order b,c,d,a as you have mentioned. The query is also using this primary unique index to return the result, still taking more time.
tab1 is having 17 million rows and the result is around 11 million rows. So direct table scan can help? If so how do i make it?

Also can this query be written in a better way to run quickly?

tonkuma · #6 (**permalink**) 05-25-10, 14:22

Quote:

no buddy, combination of a,b,c and d are unique.

unique index is created on these 4 columns.

CASE2:
then, indexes of follwings would be worth to try...
(b , c , d , a)
(e , f , b , c , d , a) or (e , f , a)

CASE3:
I wondered if your requirement might be as following...

Code:

SELECT t1.x , t1.y
  FROM tab1 t1
 WHERE
       t1.a =
       (SELECT MAX(t1a.a)
          FROM tab1 t1a
         WHERE t1a.b = t1.b
           AND t1a.c = t1.c
           AND t1a.d = t1.d
           AND t1a.e = t1.e
           AND t1a.f = t1.f
       )
   AND t1.e = const1
   AND t1.f = const2
;

dharmaraj_ganesan · #7 (**permalink**) 05-25-10, 14:30

tonk, case 3 can be ruled out as it may change logic in the program.

in case 2 we have primary index of order b,c,d,a. also my query is using it and taking more time, i can try creating a new index on ur second order e,f,b,c,d,a.

tonkuma · #8 (**permalink**) 05-25-10, 16:02

Quote:

tab1 is having 17 million rows and the result is around 11 million rows. ...

How about this?

Code:

SELECT x , y
  FROM (SELECT x , y
             , ROW_NUMBER()
                 OVER(PARTITION BY b , c , d
                          ORDER BY a DESC) AS rn
          FROM tab1
       ) q
 WHERE rn = 1
   AND e  = const1
   AND f  = const2
;

and

Index (b , c , d , a DESC , e , f)

( or Index (b , c , d , a DESC ) or Index (b , c , d , a DESC , e , f , x , y) )

Lenny77 · #9 (**permalink**) 05-25-10, 17:10

For me the next one looks good:

Code:

select tab1.x,tab1.y from tab1,
table (select max(tab1alias.a) maxA
                 tab1.b, tab1.c, tab1.d
         from   tab1 tab1alias 
         where tab1alias.b  = tab1.b 
            and tab1alias.c  = tab1.c 
            and tab1alias.d  = tab1.d) t2  
where tab1.a = t2.maxA
  and tab1.b = t2.b
  and tab1.c = t2.c
  and tab1.d = t2.d
  and tab1.e = const1 
  and tab1.f = const2

Lenny

dharmaraj_ganesan · #10 (**permalink**) 05-26-10, 09:40

@ tonk, ur query is even taking more time than the original one.

@ lenny, i tried ur query and was taking the same time.

still i didnt play with index combinations. i may need to work on that too.

any other suggestions or workaround?

thanks for support.

Internet Services

Web Development

Digital Marketing

Consumer Tech