Re: Optimiser choice of index

Colin Dawson wrote: > 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 > >> The 1st query was after drop and creae of both indexes, 2nd query is after >> update statistics high for 1st column in all indices and update statistics>> low for all other columns in all indices >> >> QUERY: >> ------ [snip] >> where >> j.cr_date between '2005-09-20 08:00:00' AND >> '2005-09-20 16:00:00' ^^^^^^^^^^^^^^^^^^^^^ This is a datetime year to second, not a date. Because the number of values this can take is so finely grained, the optimiser thinks that it must be highly selective. -- rh