Re: Optimizer of Online 7.22 selects wrong index

Helmut Leininger wrote: > > I am running Informix Online 7.22 on Bull Escala (=J30 or J40). In > some cases the optimizers chooses a wrong index for a often used query > query. Here is one case: > > My table MSTUECK has about 745000 rows and several indexes, some of > them having the first two or three columns in common. My query (WHERE > columns match ORDER BY columns and the columns of one index) runs ok > as long as I do not run an UPDATE STATISTICS against this table. It > delivers about 30 rows within 0.x seconds. > After the execution af an UPDATE STATISTICS (whatever kind, with or > without specifying columns) the optimizer get confused and makes a > SEQUENTIAL SCAN followed by a SORT. Now the same query (with the same > result) takes more than 5 minutes. > Playing with OPTCOMPIND does not change the behaviour. Also, I cannot > drop some indexes as they reflect the selection criteria/order > criteria of other important queries. Up to now, my only bypass is to > avoid running an UPDATE STATISTICS against the table (also a general > UPDATE STATISTICS without a table specification must not be run).> > Does anyone have an idea? > Helmut, It appears that your problem may lie in your indexing strategy. Queries on large tables such as yours that filter out most of the rows should have indexes composed of one or more columns in the WHERE clause. In compound indexes comprised of multiple columns, place those columns that can eliminate massive numbers of rows first. It appears that your indexing strategy for this query was to use index MSTUECK2 since it contains all of the columns in your ORDER BY clause. I wouldn't be overly concerned with performing a final sort on a result set of only 30 rows. For this particular query, you should utilize an index comprised of one or more columns in your WHERE clause. You should only be concerned with improving sort performance with an index when your query returns a large number of rows. Hope that helps, Scott ******************************************************************** * Scott C. Campbell | Voice : (703)902-8218 * * Sr. Software Development Specialist | Fax : (703)790-3391 * * Landmark Systems Corporation | E-mail: scott@landmark.com * * 8000 Towers Crescent Drive | * * Vienna, VA 22182 | http://www.landmark.com * ********************************************************************