Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2008
    Posts
    26

    Unanswered: Issue on query for joining same table.

    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.
    Last edited by dharmaraj_ganesan; 05-25-10 at 11:54.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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

  3. #3
    Join Date
    Aug 2008
    Posts
    26
    no buddy, combination of a,b,c and d are unique.

    unique index is created on these 4 columns.

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  5. #5
    Join Date
    Aug 2008
    Posts
    26
    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?

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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
    ;
    Last edited by tonkuma; 05-25-10 at 15:26. Reason: Add "or (e , f , a)"

  7. #7
    Join Date
    Aug 2008
    Posts
    26
    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.

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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) )

  9. #9
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Lightbulb But could be....

    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
    Last edited by Lenny77; 05-25-10 at 18:15.

  10. #10
    Join Date
    Aug 2008
    Posts
    26
    @ 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.

Posting Permissions

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