Re: Informix Optimiser: Should I use it?

Good 2cents Sally!!! Avoid forcing the query with DUMMY stuff if at all possible. Let the Engine do the WORK you paid ($$$$$$) for! Again version is critical when you talk this subject, as well as, the DB DESIGN!! ------------------------------------------------------------------------- Cheryl Kendricks -- NO MORE AN EXPERT THEN THE NEXT! Internet:cherylk@prod1.jcdc.doleta.gov OR cherylk@gwysmtp.jcdc.doleta.gov DTSI, Inc. Voice: 1-800-598-5008 Fax: 512-393-7296 Database Administrator - DOL Job Corps San Marcos, Texas ------------------------------------------------------------------------ On Tue, 12 Mar 1996, Sally Woolrich wrote: } 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! } ============================================================================ }