Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2003
    Posts
    1,629
    Provided Answers: 1

    Unanswered: DB2 optimizer order of executing the SQL statement

    SELECT T1.A,SUM(T2.B)
    FROM T1, T2
    WHERE T1.C=T2.C
    AND T1.D = 1
    GROUP BY T1.A

    Order of exectuting:
    1. DB2 looks to the T1, T2 tables.
    2. Execute the join
    3. Execute the where condition
    4. Sorts the data because of using GROUP BY
    5. GROUP BY the data
    6. Summarize the data.
    7. Return the data to the user.

    Is this correct?
    Does DB2 optimezer use join before where condition? Is the order in where clause correct or should I use the following SQL:

    SELECT T1.A,SUM(T2.B)
    FROM T1, T2
    AND T1.D = 1
    WHERE T1.C=T2.C
    GROUP BY T1.A

    Thanks for reply,
    Grofaty

  2. #2
    Join Date
    Jan 2003
    Posts
    1,629
    Provided Answers: 1
    Hi,

    Mistake in secound SQL. The right one is

    SELECT T1.A,SUM(T2.B)
    FROM T1, T2
    WHERE
    T1.D = 1 AND
    T1.C=T2.C
    GROUP BY T1.A

    ... where condition before join.

    Grofaty

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i'm not a db2 performance expert, but the order should not matter

    what about this --

    SELECT T1.A,SUM(T2.B)
    FROM T1 INNER JOIN T2
    on T1.C=T2.C
    WHERE T1.D = 1
    GROUP BY T1.A

  4. #4
    Join Date
    Jan 2003
    Posts
    1,629
    Provided Answers: 1
    Hi,

    I can't prove that this is right for this SQL (SQL above).

    But I have had one SQL with join of 5 tables with I switched the where conditions and join conditions and DB2 optimazer worked completly diferent. I could prove it with Visual Explain. The total cost was better with join first and then other where condition compared with other where conditions and then join.

    Does anybody know where could I read more about optimal sorting of sql pieces?

    Thanks for help.
    Grofaty

Posting Permissions

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