Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2011
    Posts
    334

    Unanswered: problem about tables join order

    hi,everybody

    I have a problem about tables join order when I tuning a query.
    the Sql is something like this :
    Code:
    select XXXXX
    FROM T1 LEFT JOIN T2
               ON T1.C1 =T2.C1
               INNER JOIN T3
               ON T1.C2 = T3.C2
               INNER JOIN T4
               ON T3.C1= T4.C1
    WHERE T4.C2 = 'xxxxxx';
    The optimizer chose a bad join order for this query:
    T4 cross join T1 and then left join T2 and then inner join T3,
    which 's cost is very high
    because T1 is a very large table。

    but if i change the join order to this
    Code:
    select XXXXX
    FROM T1  INNER JOIN T3,
               ON T1.C2 = T3.C2
               INNER JOIN T4
               ON T3.C1= T4.C1
              LEFT JOIN T2
               ON T1.C1 =T2.C1
    WHERE T4.C2 = 'xxxxxx';
    The optimizer chose a much more cheaper plan for this query:
    T4 inner join on T3 and then inner join T1 and then left join T2,
    because only one row in T4 satisfied the contiditon T4.C2 = 'xxxxxx'。
    I am puzzled about that why the optimizer can not find a best join order
    for the first SQL, why it need user to change the join order explicity ?

    Is there any difference between the first and the second sql?
    Any reply will be appreciated, Thx.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    There shouldn't be a difference. If you can reproduce it easily then you may want to open a PMR.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Ensure RUNSTATS was done against recent data.
    Try by increasing optimization class.

  4. #4
    Join Date
    Nov 2011
    Posts
    334
    Yeah, i have tried optimization class 9, but it dit not work。
    So as n_i metioned above ,maybe i need to open a pmr to IBM.
    Thx.

Posting Permissions

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