Re: query optimizator

Marco Traini wrote: > > Something strange happened to me (is not the first time) with > Informix OnLine 7.2 on UnixWare 2.1.1 with dual Pentium 133. > A query that we use frequently, suddendly stopped working. > The query optimizer decided to change something, and the query now > do not use anymore the right indexes. [SNIP] > Is it possible that table c has grown so much that the optimizer finds more > convenient starting from another table? (but I select only a few rows from > table c). > I also tried to check the indexes for table c and to drop and recreate > them... > Could it be a problem of too much levels in the index of table c? > I think this is only a problem of the query optimizer. Is this true?? > Apart from this specific case, Is it acceptable an optimizer that works this > way? There are two things you can try. 1) Update statistics HIGH ... DISTRIBUTIONS ONLY for each column that heads an index and for the first column that is different between any set of indexes that start with the same columns. Use one command for each column. Also do UPDATE STATISTICS LOW on all of the columns of each index separately (one command per index key). If this does not help then 2) select * from sysindexes where tabid = <your tables' tabids> and examine the number of levels for each. As you intimate if the cost of using two indexed paths is almost the same the optimizer will take the depth of the indexes into account when calculating the cost of various paths. If the index that is being used now has fewer levels that the one you feel it should use you CAN SAFELY update the sysindexes records to reflect a different relationship between the depths of the two indexes. ONLY THE OPTIMIZER USES THESE DEPTH figures so there is no harm in modifying them. Remember that an UPDATE STATISTICS LOW or HIGH will reset the sysindex record to reflect reality again and you may have to "FIX" it again. Art S. Kagel