Hi, I have a query like this:

select min(col1)
from tab
where somedate between 'date1' and 'date2'

Now, I have one index on column col1 and another one on somedate column. When I do runstats (I tried six different combinations) the query execution time goes up (more than couple of minutes) and access plan revealed it's using the index with col1.

I have also kept old statistics of production (a year back) and when I apply that script and then run the query again it works fine (less than one second). This time access plan revealed that it's making using of index on column somedate!!!

So, does the notion that DB2 optimizer will pick the best possible path when provided with updated statistics is wrong? Or there are some flaws in the RUNSTATS or Optimizer code?

Your comments are appreciated.

dollar

This sounds like a familiar question posted recently?

What is the clustering index or the index used for reorgs. Maybe that has changed since when you had good response time? If somedate is the clustering index, it is more likely to pick that one for access. Make sure the table is reorged, then do runstats again.

You could also try different query optimization levels (higher or lower).

Lastly, I would check for APAR's in FP4 or APAR's yet to be fixed.
http://www-3.ibm.com/cgi-bin/db2www/...lib.d2w/report

I would say, the best index for that query is:

(someday, col1)

Then you will get an index only access.

I didn't think a between could be resolved with an index only access though.

Well, the query is making use of index but not the right one. When it makes use of index with time column (one in where clause), the result is instant but when it's using the index with ID column (one in select clause), it takes over 10 minutes.


dollar

A between can use index only access, but in this case there is the min function on the selected column which is not in the index. So index only access is not possible.

You're right - I meant min and wrote between. Thank you.

Dollar you should also read the other post on indexes

http://www.dbforums.com/t979949.html

Cheers
Sathyaram