how does the optimizer work?

Hello, we ran into the the following problem with the execution path evaluation of the Informix Online 4.10 (AIX) optimizer: a table t has some indxes i1, .., ik defined. A program using t always accessed it using index i1, which was a quite good choice. (I should mention that the table is increasing in size. Update statistics is run every night). Then one day, without any change in the programs, the optimizer decided to use index t6 instead. That indeed was a bad idea, since that index was constructed for an other job and the result was a (nearly) sequential scan of the table. It took some time to find this out. We finally dropped the index i6 and now the program works again with normal speed. I am sure, there will be a good explanation why the optimizer decided to use the other index (perhaps the number of rows in the table reached some limit), but we don't know it. Therefore we always have to look what the optimizer did, and why he might have done it. A better solution would be to have some knowledge about the rules the optimizer uses for the evaluation of the execution pathes. Is this information available somewhere? -- ------------------------------------------------------------------------------- M M L CCC | Michael Schunk | Tel: +49 2102/8506-0 MM MM L C | MLC Systeme GmbH & Co. KG | Fax: +49 2102/8506-30 M M M L C | Am Bruell 19 | Eunet: ms@mlc-ratingen.de M M LLLL CCC | W 4030 Ratingen (Germany) | -------------------------------------------------------------------------------