Re: The truth behind UPDATE STATISTICS...

Hi folks, As an ex-consultant associated with DSC's Phase-1 development, I would like to share some of my experiences about UPDATE STATISTICS command. The update statistics essentially updates systable entries, and needs to be run "OUTSIDE" the user programs whenever one or more tables are updated extensively (Inserts & Deletes). This essentially optimizes the index, and makes searching more efficient. Do not attempt to execute this statement as part of your program, as it certainly locks up the table that it processes and makes it unavailable to scores of othr programs trying to access the table. It is possible to update statistics on a single table or on the whole database. Please see the syntax of usage in informix reference manuals. Alternatively you may DROP and re-CREATE index for the affected table through a dbaccess query. You may consider to set this one up as a user invocable menu option which will bring down the whole system for a brief period of time perform necessary updates and re-start all services automatically. A number of SQL -246 (Cannot read via index) errors may get corrected after you execute this procedure and your system will perform much better. Thanx. Ravi.