Re: UPDATE STATISTICS

> > Hi, > > I guess it does not write outputfile. It > is a good idea to run after a bulk load of > data,also you can incorporate it with your daily > archiving script that is before archiving you can > run an Update Statistics. > > Hope this helps !! > > Cheers > > Patrick Stacey wrote: > > > > 1. Can someone tell me if the UPDATE STATISTICS command generates an > > output file for the DBA to read? > > > > 2. How often should you run this procedure? > > > > TIA, > > 1 - In current versions of Online update statistics can generate distribution info on the data in each table (RTFM for more info). This is kept in the sysmaster:sysdistributions table or some such. I believe it is reportable using dbschema - but won't swear to it. 2 - How often? Depends on your application. If the table in question is static then there is no need to continuallly update statistics for it. If the table is dynamic then fresh statistics are vital for performance. For example, if you create a table statistics indicate that the table is empty - then you load it with 2000 rows - according to statistics the table is still empty. Now try doing a read on the table - the optimizer sees that the table is empty and hence it would be faster to read the table sequentially than to do the extra read required to get an index. Ouch. Granted that is a severe case. At the same time doing an UPDATE STATISTICS HIGH can be expensive. UPDATE STATISTICS itself is generally fairly cheap. With the current syntax you can control exactly what level of update statistics you wish to do for each table. While you're at it don't forget to update stats for SPLs. cheers j. ________________________________________________________________________ jparker@informix.com Burlington, MA, USA (x1619) "If anything can go wrong, fix it - to hell with Murphy." ________________________________________________________________________ Any opinions expressed herein are my own and not those of my employers.