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. Why would you want to try and circumvent the Informix optimizer??? I'm quite sure the gurus at Informix who wrote the optimizer aren't exactly college co-ops or something. I think if you do something like this, you probably should be using flat files or some lesser DBMS IMHO. -- Melvin Mariney, Informix DBA e-mail: melvin.mariney2@bridge.bellsouth.com