Re: Informix Optimiser: Should I use it?

Peter Wotherspoon (peter@kirzel.demon.co.uk) wrote: : 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 Informix 5.01 did have a nasty bug where update statics put negative values in the NPU column of the database. Update statics would basically put the wrong values in the systables column and cause the optimser to think it would do sequential searches in the database. Our vendir had a stored procedure that corrected the negative npu in the table which was run every 30 minutes. The problem was fixed by going to version 5.04uc4, call informix and ask for a patch to go to version 5.04, but check with your vendor first. And if your vendor does have a problem going to version 5.04 then ask informix about how to fix the negative NPUs in a stored procedure its a KNOWN bug. -- .....|\\/|...|\\....... mcdavey@crl.com .....|..|...|/.......