Re: Index problem

In article <6nd3vh$dsr$1@news.xmission.com>, MOPSOMER@raychem.com writes > > 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) > Do not use ORs they result in seqential scans. Use UNION or UNION ALL instead. > select .... where a=1 and c=3 > It says in the manual that you really need an index on a and c for this query. Informix only looks at leading columns in the index which match the where clause. I imagine that the a,b,d index is the first index created i.e. the first index starting with a,b that it finds. Create an index on a,c for this query... > 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 -- David Williams Maintainer of the Informix FAQ Primary site (Beta Version) http://www.smooth1.demon.co.uk Official site http://www.iiug.org/techinfo/faq/faq_top.html I see you standin', Standin' on your own, It's such a lonely place for you, For you to be If you need a shoulder, Or if you need a friend, I'll be here standing, Until the bitter end... So don't chastise me Or think I, I mean you harm... All I ever wanted Was for you To know that I care