Informix Optimiser: Should I use it?
Posted in 1996
This may seem like a strange question, but our software supplier has told us that we must on no account update the database statistics, as it will (a) make queries run more slowly, and (b) make the rows return in the wrong order - they rely upon the implied order of rows returned by their chosen index rather than using "order by". They force Informix to use their chosen index by declaring a dummy data column, with a standard value (always "0"), and putting this at the start of the "where..." clause (eg. "where index_dummy1 = "0" ...). The reason we mustn't run "update statistics" is that the optimiser might decide to use a different index if it's allowed to know how many rows are in each table. This means that they are effectively bypassing the Informix optimiser by specifying their own access paths. An Informix consultant who has spoken to one of my colleagues (now sadly departed to work for Sequent) does not seem overly impressed by the software supplier's approach. I am interested to know whether ANYONE else thinks it's a good idea. Peter Wotherspoon