Re: Index problem

Ian Briscoe wrote: > > Is there a way to force Informix Online DS (7.13) to use a particular > index. > > Consider the query: > select.... where a=1,b=2,c=3,d=4 > > I have a composite index on a,b and c > I have another index on a,b and say, e - which isn't in the original query. > Informix is using that second index and causing a major slow down of the > system. I've tried dropping and recreating indexes and updating statistics > but to no avail. All things being equal and the stats not indicating otherwise, the optimizer will tend to select the index with the fewest levels. You can trick it by updating the sysindexes records for these two indexes to change the ratio of the number of levels of one to the other. I have been assured by Informix people that this is not used anywhere else in the code and is a harmless kludge. One problem is that IDS versions <7.24 silently ignore columns beyond the second column in a multi-colummn index so the cost of using both of your indexes is probably the same. Versions 7.24 and 7.3 fixed this problem. It was supposed to be an optimizer option to avoid unneccessary index I/O when the index's filter value in reducing the number of data pages to scan did not outweigh the I/O cost of reading the index pages. Unfortunately this bug caused the optimizer (since 7.10) to ALWAYS select this option. Version 7.3 is the first version with optimizer hints and other similar enhancements. Art S. Kagel