Re: Q: optimizer does the wrong thing on join

I recently wrote a 14 page article about understanding the Informix optimizer - first published in the NSW ((Australia) Informix User Group Newsletter in Nov 95, now available on the International Informix User Group WWW Page (iiug.www.org). The suggestion made in this thread to repeat the where clause in order to influence the optimizer originally came from someone at Informix (in the team responsible for the optimizer at Menlo Park). The "trick" eventually made its way to the FAQ. However this same person who I spoke with (to review the optimizer article for accuracy) wished he had never made the suggestion which is now so often repeated. So, for the benefit of those who have not read the article; please take note that the suggestion of repeating the where clause to influence the optimizer is no longer recommended and may cause problems in the future. In fact in DSA version 7 it will NOT work as the "query re-write" feature eliminates duplicates in the where clause. (Kerry may want to update the FAQ). The optimizer article which is on the web is intended to be a "living" document and will be updated as necessary. Please feel free to forward any additional tips, comments, criticisms or suggestions to me (at nourg@acslink.net.au) so that they can be included in future versions of the article. Regarding Dwight Rudolph Jr's original post - the optimizer may in fact be doing the "right thing" (the least expensive option) depending on the indexes available. For example performing a sequential scan on the large table first can be a lot better than performing a sequential scan repeatedly for each row in the subset obtained from smaller tables. Unfortunately the original post did not contain enough information (like table structure, query, etc) to confirm whether this is the reason for the unexpected choice. dwight@rudolph.com (Dwight Rudolph Jr) wrote: >In article <351.050296@deckard.hb.north.de>, ppr@deckard.hb.north.de (Patrick Preuss) says: >> >>Hi, >> >>we have a join with several tables, written with the most restricting table >>stands at the top (the right thing, I guess). >>What does the optimizer do? He just flips the order, and scans the biggest >>table first, sequentially!!! Is this a problem for Update Statistics? > >Check the Informix FAQ, section 5.8. It says you can often trick the >optimizer into starting where YOU want it to, by listing the >relevant constraint three (yes, three) times. e.g., > AND hamster.shoesize = 6 > AND hamster.shoesize = 6 > AND hamster.shoesize = 6 > >We tried it on our problem queries. It made a huge difference for us, >so we coded it into the application then went outside to throw up. > >dwight Copyright 1996, All rights reserved. __________________________________________________________ | o o | | Gavin Nour: nourg@acslink.net.au | | Director: Select Software Solutions Pty Ltd. | | Secretary: Informix User Group NSW, Australia. | | Committee member: International Informix User Group. | | Mobile: +(61) 0419 803 113 | | o o | |__________________________________________________________|