UPDATE STATISTICS

You can use the Update Statistics Wizard to generate a script containing UPDATE STATISTICS SQL statements for a selected database.  Running this script periodically can significantly  improve a performance of SQL queries executed against this server.

UPDATE STATISTICS statement is used to:

• update the system catalog tables that the server uses to optimize queries
• determine the distribution of column values
• force reorganization of stored procedures
• convert existing table indexes when you upgrade the database server

The wizard allows you to specify the objects for which you want to generate update statistics statements and configure all available UPDATE STATISTICS options.

There are multiple ways to open the Update Statistics wizard:

• From Tools->Update Statistics top-level menu
• Using Update Statistics popup menu when a database node is selected in the Object Explorer tree
• Using Update Statistics popup menu when the Tables folder is selected in the Object Explorer tree
• Using Update Statistics popup menu when the Routines folder is selected in the Object Explorer tree
• Using Maintenance->Update Statistics popup menu when an individual table or routine is selected in the Object Explorer tree

The first page of the wizard allows you to choose a database server connection and specify the scope of objects for which to generate UPDATE STATISTICS statements. This page is not opened when you use UPDATE STATISTICS popup menu from the Tables node in the Object Explorer or from an individual table node. 

Choose All Tables and Procedures if you want to generate one UPDATE STATISTICS statement for all tables and one UPDATE STATISTICS statement for all procedures in the selected database.

For example,

UPDATE STATISTICS MEDIUM FOR TABLE;
UPDATE STATISTICS FOR PROCEDURE;

Choose Specify a List of Objects option, if you want to generate a separate UPDATE STATISTICS statement for each table in a database or for selected group of tables or routines. This option gives you most flexibility in modifying the UPDATE STATISTICS parameters for individual tables. You can also configure column-level parameters for individual tables.

For example,

UPDATE STATISTICS HIGH FOR TABLE tbl_assembly RESOLUTION 4.0;
UPDATE STATISTICS HIGH FOR TABLE tbl_order RESOLUTION 4.0;
UPDATE STATISTICS LOW FOR TABLE tbl_orderdetail (id_order, itempartnumber);
UPDATE STATISTICS FOR PROCEDURE add_customer_order;

The Object Selection page allows you select list of objects - tables, synonyms and procedures - for which to generate UPDATE STATISTICS commands. You can use Filter parameters such as Tables, Routines, Synonyms checkboxes and Filter By dropdown list to limit number of objects in a database displayed in the list. Use checkbox at the left of the object name to include the object. Use Select All button to include all objects currently displayed in the filtered list. If you choose only a single table then the next page will allow you to select a column-level UPDATE STATISTICS command options for this table.

The Update Statistics Options page allows you to select different options available for this SQL statement.
See IBM Informix Guide to SQL - Syntax for detailed information on all available options of UPDATE STATISTICS statement.

The final page displays the generated script containing multiple UPDATE STATISTICS statement and allows you to execute this script against the database server, save an entire script in a text file or copy portions of the script to be pasted into any SQL Editor or text editor.