Re: understanding On-Line's Optimizer

At 10:05 AM 19-12-96 -0500, you wrote: >I am new the Informix On-Line engine. I am looking for information on >On-Line v7.13 Cost Based Optimizer. We are migrating to Informix from >SQLBase and the optimizers work differently. In informix, we often get >sequential scans on queries that we feel should be using an index. > >Do you know of any books or white papers on the subject? There is a good introductory chapter on Cost-Based Optimizers in Dr. Stonebraker's book "ORDBMS: The Next Wave." (Sorry, I am traveling at the moment and don't have the ISBN number.) I keep that chapter bookmarked in my hard copy for quick reference. You can get a sneak preview on the Informix Web Site, under the white papers section. While I'm at it, that book is a good starting point for information on the Universal Server. Though the material on Illustra database management won't apply, the concepts for the objects and datablades will all translate directly into Informix. I believe there was a presentation at the Informix User Conference about three years ago, by Jeff Fried, on "Understanding the Optimizer." You may be able to retrieve the notes from this presentation. The cdi archives, available through the outstanding support of the International Informix Users Group has many references to the optimizer, and specifically tips on how to get it to use a particular query strategy. (The one *you* want rather than the one *it* wants.) See their site at http::www.iiug.org. If you are using the Informix MetaCube product you have the option of manually influencing the cost of access, which allows you great flexibility. Finally, TFM offers a great deal of information. The "Informix Guide to SQL Tutorial" has an entire chapter on the optimizer. The chapters on the "Set Explain" statement are all about the optimizer, and how to discover what the optimizer is doing and why. A few hours spent with sqexplain.out files will yield more learning than all the books in the world, all of it directly applicable to your application. That is a start, anyway. Good luck! ______________________________________________________ Clem Akins (aka clema@informix.com) Informix Software, Inc (Standard disclaimers apply) International Technical Support Last seen: On Formosa! (Know where that is?)