Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2014
    Posts
    5

    Unanswered: db2 query tuning

    The below specific query is taking double the expected time, could you please let me know if there is any better way to write the below query,

    SELECT AA.1, AA_2, BB.1,BB.2..
    FROM
    TABLE_1 AA
    LEFT OUTER JOIN
    TABLE_2 BB
    ON
    (
    BB.REF_ID = AA.REF_ID
    AND
    BB.BR_ID = AA.BR_ID)
    LEFT OUTER JOIN
    TABLE_3 CC
    ON
    (AA.ACCT_ID = CC.ACCT_ID
    AND
    AA.CL_ID = CC.CL_ID)
    LEFT OUTER JOIN
    TABLE_4 DD
    ON
    (AA.REF_ID = DD.REF_ID
    AND AA.BR_ID = DD.BR_ID
    AND AA.CL_ID = DD.CL_ID)
    LEFT OUTER JOIN
    TABLE_5 EE
    ON
    AA.REF_ID = EE.REF_ID
    ORDER BY
    F_ID
    WITH UR;

    I have indexes on Table_1 on columns REF_ID,BR_ID,ACCT_ID but not on CL_ID
    rest of the tables TABLE_2, TABLE_3, TABLE_4 and TABLE_5 have the indexes on the columns used in the where clause.

    Thanks
    Last edited by rock_001; 09-08-14 at 12:33.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by rock_001 View Post
    ...
    ...
    LEFT OUTER JOIN
    TABLE_5 EE
    ON
    AA.REF_ID = DD.REF_ID
    ...
    This part looks strange for me ...

  3. #3
    Join Date
    Aug 2014
    Posts
    5
    Oh sorry, its corrected

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    you mention you have these different indexes on each of the tables. Are they being used? What does your explain show being done?
    From what you show above, it doesn't seem like there would be any performance issues with the appropriate indexes on each table. Though, there is no WHERE clause to filter what is returned so you are getting ALL rows from your first table and then all rows from each of the other tables where the conditions match.
    Dave

Posting Permissions

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