Re: Optimizer Info

>Are there any ways to know how the optimizer is evaluating all the >possible query plans and how it decides which one it chooses. > >I know about 'set explain' and data distributions and such, but is there >a way to see the process >the optimizer went through. Basically, no. The output of the SET EXPLAIN command is as close as a customer can come to seeing all the query plans the optimizer evaluated, the costs associated with each, and the reasons for its choice. >I guess I am looking for a trace of the optimizer and not just the end >result. > >We are in the process of doing some serious analysis of our query plans >and we are trying to see what influences the optimizer etc. OTOH, you could conceivably hire a low-level Informix consultant to come and discuss the optimizer with you and run a debuggable engine on site. The cost would not be trivial, however. If you are in North America there are a couple of organizations within Informix that might be able to do this, including R&D, ATG (Advanced Technical Group), RAS (Regional Advanced Support), and the RT (Resolution Team.) Call your local support office and discuss these options with the management there. >Also can you 'tell' or hint the optimizer what order to do joins >or what indexes you would like to use, or is it just up to the >optimizer. In version 7.3 (coming soon!) you will be able to provide optimizer hints, including "prefer" and "avoid" join methods, indexes, and such. >I realize the way SQL is contructed will affect the plan chosen, however >I think other DBMSs offer >the ability to indicate the indexes to use via the SQL. > >Books and/or other resources would be helpful. Perhaps you are already beyond this level, but there is a tidy little description of relational optimizers in Michael Stonebraker's book "Object-Relational DBMSs" (ISBN 1-55860-397-2). He makes reference to "System R" by Pat Selinger et al., which may be a helpful pointer as well. While you're at it, see the library of the Association for Computing Machinery for deep treatises on all manner of issues. http://www.acm.org . HTH, ___________________________________________________________ Clem Akins (aka clem@informix.com) Informix Software, Inc (Standard disclaimers apply) International Technical Support Last seen: Preparing for a weekend of summer beachtime in Oz.