Re: query optimizator

>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. >The query is like this: >select d.x, d.y, sum(a.x) >from a,b,c,d >where <conditions> >group by 1,2 >order by 1,2;>The tables a,c have been growing a lot each day adding new records. >We perform weekly update statistics medium on all the fields of all >the tables and high on ALL the fields involved in the conditions of tables >a,c. >This was OK for one year 'till yesterday. >The query ought to start from table c to select the records and then apply >the other conditions. Yesterday this changed: the query starts from table a >with >sequential scan. I must duplicate the conditions on table a to let the >query use some index for this table. I was not able to make the query start >again from table c. >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? >Is this a frequent case? >I'm administring Informix since two years, and the optimization of the >queries is >the thing I found most difficult: there is nothing certain about the update >statistics and the optimizer, even if you follow the directions of the >manuals. >If it's not so (and I hope this) please give me some suggest before my chief >decides to change to Oracle..... > >Marco Traini Which version? You might need to drop the distributions. Madison Pruet