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

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.

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

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

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.