Re: Informix Optimiser: Should I use it?

Cheryl Kendricks, molten keyboard in hand, chimes in with: > > 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!! Yup! Keep the tricks in the case next to the black hat and the rabbit. > On Tue, 12 Mar 1996, Sally Woolrich wrote: > > } In article <1Oq7jDAgZ1PxEwzH@kirzel.demon.co.uk> > } peter@kirzel.demon.co.uk "Peter Wotherspoon" writes: > } [ vendor relies on static statistics, deleted ] > } 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. I have, in my day, thought about the idea of loading up a "perfect" database, updating statistics, and then emptying everything out to make room for the production data. It is really, a BAD idea though. The optimizer tends to do a pretty good job when trying to resolve queries. I've seen moments of sheer brilliance come of the optimizer that just leaves my jaw slack and eyes wide. Of course, I've seen moments of astounding stupidity out of the optimizer as well, leave my face in a similiar state. But, if someone relys on a specific set of statistics, then you are doomed from the start. If there is every a problem with the database, you'll have difficulty restoring your "baseline" statistics after you've been using it for a year or two. Also, if you ever decided to use your database for some new projects, or add some extra tables, then you can't take advantage of the optimizer for YOUR queries because your statistics are locked back into the stone age can't be changed. We won't go down the path of that "new" DBA you just hired who decides to "UPDATE STATISTICS" because the books suggest it. Who knows what will happen if you upgrade your Informix engine. How does the database work with fragmented tables, PDQ, etc? Everyone has had to coerce queries to convince the optimizer to take the enlightened path to performance. Either through ORDER BYs, or breaking up the query into temp tables (like in the massive sub-query thread), etc. The best part of SQL is that it, most of the time, takes the access issues out of your hands and deals with them for you. A lot of old ISAM folks HATE that detail. They want total control. Static statistics are thin ice. What happens when that ice breaks? Will Hartung (villy@collinscomp.com)