Re: when to 'update statistics' ???

Hi Leo, >Leo Chen wrote: > if anyone knows when to do the 'update statistics' ? > Or, if there is any information (some ratios, for example) indicates >that the engine needs 'update statistics'?? TFM (Informix Guide to SQL: Syntax, version 7.22, under the heading for UPDATE STATISTICS) states: "Update the statistics when you perform extensive modifications to a table or when changes are made to tables that are used by one or more procedures, and you do not want the database server to reoptimize the procedure at execution time. If your application makes many modifications to the data in a particular table, update the system catalog table data for that table routinely with the UPDATE STATISTICS statement to improve the efficiency of queries. Manyis relative to the resolution of the distributions. In addition, if the data changes do not change the distribution of column values, you do not need to execute UPDATE STATISTICS again." In other words, run it when the number of rows in the table changes significantly, or when the nature of the data in the rows changes significantly. If your application runs constantly, gathering the same kinds of data and deleting old data as fast as it inserts new data (as might be found in a 24x7 manufacturing data-collection application) then you might not need to run that command at all after the database reaches size stability. There are lots of DBAs who choose to run this command from cron, even nightly, but that is often unwarranted. This comes from the new, orderable Informix CD-Doc version 7.22-CD1 which, though not perfect, is even better than my old Beta copy. Good luck, ______________________________________________________ Clem Akins (aka clema@informix.com) Informix Software, Inc (Standard disclaimers apply) International Technical Support Last seen: Back in Menlo Park