Re[2]: Index problem

The suggestion made by Art to update the sysindexes table manually to force the optimizer to take another index looks very attractive to me. Recreating indexes and update statistics high did not help in my case. We are an SAP site running Informix 7.21 in production and 7.24 in our QA systems. As all SAP indexes are composite indexes and as we have a couple of large tables (> 10 GB), we regularly experience bad runtimes for certain SQL-statements because the optimizer takes the wrong path. The access path choosen by the optimizer for Ian's query in release 7.21 and also in release 7.24 is still sometimes not the best one. In both releases, SELECTs such as the following ones might also choose the bad index (a,b,d), while index (a,b,c) is probably a lot better. select .... where a=1 and b=2 and (c=3 or c=4) select .... where a=1 and c=3 I have been doing some tests with Informix 7.30.UC3 and I noticed that a lot of the optimizer problems have been corrected. The only problem is that it will probably still take a long time before release 7.30 is approved by SAP and then installed in our production SAP environment. So I would like to give it a try with Art's suggestion if nobody knows major problems of doing so (updating certain fields in the sysindexes table which are normally only updated when running update statistics). Mario Opsomer ______________________________ Reply Separator _________________________________ Subject: Re: Index problem Author: kagel@bloomberg.net at internet Date: 29/6/98 14:08 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