Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2002
    Posts
    456

    Unanswered: One index v/s another

    Hi,

    Is there way to tell the optimizer to use one index and not the other. I have an SQL and when i create an access plan it picks up an index which we don't it to, so how can we force optimizer to pick the index of our choice?

    DB2 UDB V8 FP3

    dollar

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Obviously, you cannot explicitly force DB2 to use a specific index over another in the SQL statement. Whether it is possible to influence DB2 depends on several factors.

    If you give the table and index DDL with statistics (row count) and some information about distribution of index values (full key card), and the SQL statement in question, then we may be able to help.

    Also, please explain why you want DB2 to use an index it does not ordinarily choose.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Sep 2002
    Posts
    456
    Well, here is the query:

    SELECT MIN(test) FROM test WHERE stime BETWEEN '2004-01-08-00.00.00' AND '2004-01-08-23.59.59'

    we have indexes both on test and stime column respectively. When I create the explain it shows me that it is picking the index created on column test, BUT I want it to pick the one on stime, as it makes it faster.

    HTH to understand the issue. The stat on the table and indexes are current.

    dollar

    Originally posted by Marcus_A
    Obviously, you cannot explicitly force DB2 to use a specific index over another in the SQL statement. Whether it is possible to influence DB2 depends on several factors.

    If you give the table and index DDL with statistics (row count) and some information about distribution of index values (full key card), and the SQL statement in question, then we may be able to help.

    Also, please explain why you want DB2 to use an index it does not ordinarily choose.

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I do not know if this will help, and I have not had the opportunity to read the article in detail yet, but I was notified of this article just this morning:

    http://www-106.ibm.com/developerwork...yip/index.html

    It is supposed to be a way to influence the optimizer.

    Andy

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Try defining "stime" as the clustering index (or at least use that index to do the reorgs). After a reorg, do runstats again and retry. make sure you get full statistics on the table and indexes (including column distribution) when doing runtstats.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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