Re: Optimiser choice of index

Well, I appreciate the IDS optimiser but I think it is not infallible, I had the same situation three or four times in a ten year time-frame it is not that bad (specially if compared with sql-server ;) ) J. Colin Dawson escribis: > 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 sending to informix-list