Re: Update Statistics - 7.23

In article <33FC6D7D.66CA@bloomberg.com>, "Art S. Kagel" <kagel@bloomberg.com> writes >Mark Taylor wrote: >> >> Hi, >> >> We are looking for input on running Update Statistics on some reasonably >> large tables (approximately 80 million rows) and are looking for a >> combination of reasonable speed and reasonably accruate results. We >> have the methods in the Informix Documentation and are not getting the >> results we need. Anybody have any suggestions. The DBs are 7.23 on Sun >> Solaris 2.5 platform. > >What do you mean you are not getting the results that you need. Comared >to 5.0x? Compared to what? What are your needs? Need to force the >server to not sort? What? > >In general the recommendations in the release notes file SERVERS_7.2 >are the most up to date and are USUALLY good enough. However, the 7.2x >optimizer is MUCH smarter than the 5.0x optimizer (and even improved >over the optimizer in 7.1x) and will often decide that, based on the >statistics and distributions, using a different index and sorting will >fetch the total select set more quickly and it is usually correct. If >you, rather, need the first few rows quickly this can be death. When >this is the case sometimes updating statistics HIGH on all columns helps >but usually you will have to drop distributions and update statistics >LOW so that the optimizer behaves like the 5.0x optimizer did. > >Please give us more details and someone will try to help. > >Art S. Kagel Yep, - database schema for relevant tables - number of rows per table - Run query in dbaccess with SET EXPLAIN ON first and send us the sqexplain.out file. PS What are the improvement sin the optimizer over 7.1? I havn't seen any mentioned are they just bug fixes? -- David Williams