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

    Unanswered: db2 optimizer is taking inappropriate index

    Hi,

    We have one query running for hours and not completing. After analyzing we found one index(eg: Index1) was culprit, after dropping that index query ran within 15 mins(using another index eg: Index2).

    But we cannot permanently remove Index1, as it is required for few other queries.

    So is there any way for us to make the query always use Index2.

    We tried reorg by choosing Index2, but also no effect.

    Any suggestions are welcome!!!!

    Thanks,
    Dharmaraj.G

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Which DB2 version and OS?

    Andy

  3. #3
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    I am not sure but you can try

    - to use design/index advisor on this query and see what it says;
    - if a query is in a package you can try to change optimization level when you bind your package and it might change the indexes it is using;

    my 5 cents
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  4. #4
    Join Date
    Aug 2008
    Posts
    26
    @ Andy

    DB2 version is 8.1 fixpack 12
    OS version is AIX 5.3

    @Markham

    The query is not from package, it is a dynamic query and index advisor didnt help us...

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You can try to use the SELECTIVITY clause:

    DB2 Universal Database

    Andy

  6. #6
    Join Date
    Aug 2008
    Posts
    26
    Thanks for your reply. I am going through SELECTIVITY clause.

    Will execute and get back!!

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You can also play with the statistics for both indexes. (Which raises the question whether your statistics are up to date in the first place.)
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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