Re: Online V4.0 Optimizer

In article <1428@ixos.de> jochen@ixos.de (Jochen Hager) writes: >Hello Informix Specialists! > >Again and again we encounter optimizing unpredictables with >Informix Online V4.0. > >I know this is a very vague question but: > > Does anybody know which strategies are used by the > Informix Online optimizer? > >If anybody knows tricks to outsmart the optimizer please send me >a message, especially as to how Informix makes use of user defined >indices. 4.0+ releases use a cost-based optimizer that is simple in its complexity ;-) It will examine all the possible ways of performing the query, using all available and usable indexes, sequential reads, etc. Based on the statistics (don't forget to run that UPDATE STATISTICS on occasion) maintained in the system catalogs, it will assign relative costs to the different access methods. These costs are totalled for each possible query path, and the cheapest path is selected. You can use the SQL command SET EXPLAIN ON to see the chosen query plan. It will write the query plan in readable format to the file "sqexplain.out". SET EXPLAIN OFF will stop that function. You can then see what indexeswere used to access the query. There really isn't a way to "outsmart" the cost-based optimizer. Remember that the statistics are important (out-of-date statistics can result in inaccurate cost values). Dave Kosenko Informix Software, Inc. -- Disclaimer: These opinions are not those of Informix Software, Inc. ************************************************************************** The heart and the mind on a parallel course, never the two shall meet. -E. Saliers