Optimiser choice of index

IDS 7.31.FD7 Solaris 9 We have a query that rans in <1 sec using a 2 column composite index (id & date). I fragmented the table (it was getting close to the 16777215 page limit) and added another index (date). The query now takes >5 mins to run, a set explain confirmed the optimiser was using the new index. Adding anoptimiser directive to use the original index solved the problem. BTW Update Statistics was run after the fragmentation and index build. My question is this: Why would the optimiser use the new index on date only when using the index on id and date is obviously better? I have a vague recollection about Informix selecting the latest created index when a column appears in more than one index but can't remember which version of OnLine it was. All observations gratefully received Regards Colin There are 10 types of people in the world, those that understand binary and those that don't sending to informix-list

1/ Bug in the optimiser 2/ The Update Statistics strategy you used did not obtain appropriate information to provide the optimser with enough detail to choose the right index

Colin Dawson wrote: > IDS 7.31.FD7 > Solaris 9 > > > We have a query that rans in <1 sec using a 2 column composite index (id & > date). I fragmented the table (it was getting close to the 16777215 page > limit) and added another index (date). The query now takes >5 mins to run, a > set explain confirmed the optimiser was using the new index. Adding an> optimiser directive to use the original index solved the problem. > > BTW Update Statistics was run after the fragmentation and index build. > > My question is this: > Why would the optimiser use the new index on date only when using the index > on id and date is obviously better? > > I have a vague recollection about Informix selecting the latest created > index when a column appears in more than one index but can't remember which > version of OnLine it was. > > All observations gratefully received > > > > > Regards > > Colin > > There are 10 types of people in the world, those that understand binary and > those that don't > sending to informix-list One possible reason is some kind of bug. Another has to do with statistics... Datetimes tend to cause this. You probably have a lot more occurrences with a certain id than within a certain time interval. You could try to increment/decrment the detail of the histograms created for the second index... It has to do with estimated costs, and estimated number of rows... as the name suggests they're just an estimate... not the real thing and this can lead to situations like this. Adding 0 seconds to the datetime column may also work around the problem. Regards.