Re: Query Optimiser

->Date: Mon, 18 Oct 93 18:24:46 +0800 ->From: Richard Ridley <rridley@sin-pss.dhl.com> ->To: informix-list@rmy.emory.edu ->Subject: Re: Query Optimiser -> ...stuff omitted... -> ->We have had some experience with queries working okay under 4.X ->Online and suddenly becoming disastrously slow under 5.X, for these ->reasons. Usually, however, we have checked the code and discovered ->that if you re-arrange it more logically, the optimizer decides to ->use the index. -> ->Hope this helps, -> ->Richard Ridley In Oracle, the ANDed conditions in a WHERE tend to be done in reverse order, so you want to put your most selective join last. I don't remember if this is the case in Informix or not. Is the *logical arrangement* you refer to defined in the documentation? (Disclaimer: I don't have the most recent manuals, so maybe this has been fixed by new documents.) It seems to me that logical arrangement can be a pretty subjective judgement, and will often vary from problem to problem. For example: customer-order-item.ordered and state-county-city have pretty much the same hierarchy structure. When the optimizer does not have problem domain knowledge, I might expect it to treat the two similarly. However, customer ID is probably most selective in the first (unless you have LOTS of customers and a very small product line), while city name (based on city and county names in the US) is probably most selective in the second. I know that the Informix optimizer uses statistics for part of its optimization. Does it also use statistics for re-ordering the evaluation of ANDed joins in the WHERE clause? Regards, Alan ___________________________ ______________________| R. Alan Popiel |__________________________ \\ Internet: | Martin Marietta, Tech Ops | / \\ alan@den.mmc.com | P.O. Box 179, M/S 5422 | Std disclaimers apply. / )Voice: | Denver, CO 80201-0179 USA | ( / 303-977-9998 |___________________________| (But you knew that!) \\ /________________________) (____________________________\\