Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2009

    Unanswered: Different query plan for same query on different databases

    I have one query which is complex in nature and using 5 tables with 5-6 where clause conditions.

    The same query fires index on one of the table but in an another database of same structure the same index doesn't gets fired.

  2. #2
    Join Date
    Apr 2008
    Iasi, Romania
    Provided Answers: 3
    It is quite normal. If the number of records / number of distinct values / number of table and index pages is different, Query Optimizer might choose different access paths for the same query. Actually, the access path might be different on the same database server too, but at different time moments.
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  3. #3
    Join Date
    Jun 2007
    Have you tried using update index statistics tablename ?


  4. #4
    Join Date
    Jun 2009
    South Africa
    Normal "routine" maintenance like index rebuilds and / or reorgs could also help to get the optimizer to favor the index.

    If Cluster Ratios for index pages indicates that the data in indexes are fragmented, the optimizer might decide that a table scan or using a different index might be more efficient.

    In some cases where you have composite keys/indexes you might want to update the statistics using "update index statistics .." instead of just the usual "update statistics .." command as Mike mentioned.

Posting Permissions

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