Checking and Updating Statistics for a Single Table in CCMS (Informix) 

Use

You can use this procedure to do the following for a single table in your Informix database:

·        Check whether the table’s statistics deviate significantly from the current value.

·        Update the statistics for the table, if required.

Prerequisites

You are ready to use CCMS. Refer to Getting Started in CCMS with Informix DBA.

Use this procedure when you determine there is a need to update the statistics for a table. For example, use this procedure in the following situations:

·        You or SAP’s EarlyWatch determine that a table needs updated statistics.

·        You change the data of a table significantly (for example, with batch input).

Procedure

...

       1.      Choose CCMS ® DB Administration ® Cost based optimizer ® Create statistics.

The table you enter in the next step should meet one of the qualifications listed above in “Prerequisites”.

       2.      Enter the name of the table in TableName and choose Continue.

You see the details of the last statistics update, that is, Date, Time, and Accuracy. The results of the last statistics update are displayed in Statistic under Number of rows.

       3.      To check whether the table’s statistics need refreshing, choose Count total number of rows.

You see the current number of rows in the table in the field Total. The deviation between the current number of rows in the table and the value held in the table’s statistics is shown as a percentage in the Deviation field.

       4.      If the table’s statistics need to be updated, select the Accuracy you require.

       5.      Choose Refresh to perform update statistics.

Result

The Informix optimizer now has up-to-date statistics for the table, resulting in optimal execution of queries against the table.

 

See also:

Update Statistics in CCMS (Informix)

Configuring Update Statistics in CCMS (Informix)

Update Statistics with SAPDBA