Re: Informix Optimiser: Should I use it?

In article <1Oq7jDAgZ1PxEwzH@kirzel.demon.co.uk> peter@kirzel.demon.co.uk "Peter Wotherspoon" writes: > 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 > I think you need a new Informix consultant! Sometimes it's worth forcing the index, but I do this by using the 'ORDER BY' in conjunction with the indexes with the aim of avoiding using temp tables. Some of this may depend on the version of the engine you are using (not stated above) and maybe the supplier has come to grief with getting different answers with different version - we have - but the above solution strikes me as a potential minefield. Just my tuppence-worth! -- ============================================================================ Sally Woolrich | This mail contains my personal sally@excelsis.demon.co.uk | views not those of my employer! ============================================================================