Re: Informix Optimiser: Should I use it?

Give credit where credit is due. If my memory servers me correctly the optimizer does more then look at the stats!!!! Informix 7.X ONLINE allows you to determine the level of stats you even look at to access the data. For those DBAs who have NEVER UPD STATS and suddenly one day do, you might be surprised at the effienct query processing that suddenly occurs. So as far as I'm concern NEVER issuing a UPD STAT on a ONLINE INFORMIX DB, you might be digging a HOLE for you and your user's! From a DBA point of view trying to tune an engine that all it ever needed was UPD STAT command! LIKE I SAID BEFORE YOU PAID FOR that black hat and the rabbit. LET IT WORK FOR YOU, YOU NOT FOR IT, otherwise you've wasted your MONEY!!! ------------------------------------------------------------------------- Cheryl Kendricks 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 Wed, 5 Jun 1996, Will Hartung - Master Rallyeist wrote: } 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) } }