Updating Statistics for One Table with SAPDBA

Updating Statistics for One Table with SAPDBA

Use

You can use SAPDBA for Informix to update statistics on a single table, so making sure that the query optimizer for the Informix database has up-to-date information when it optimizes queries at execution time. For more information, see Update Statistics with SAPDBA.

Prerequisites

SAPDBA is installed correctly. Refer to SAPDBA Setup.

You know how to use SAPDBA and have logged on. Refer to Starting SAPDBA.

Procedure

Choose Update Statistics ® Update Statistics for One Table in SAPDBA for Informix.

This is identical to the report you can obtain by selecting one of the numbers on the left from the Listing Tables with Deviation with SAPDBA or Listing Tables with Non-Updated Indexes with SAPDBA.

After choosing this action, you see a screen with the following possibilities:

The table name field specifies the name for the table you want to process. If you are not sure which name you require, you may use a partial entry (for example, sta* ).

The deviation field specifies the threshold for how much the table’s statistics differ from the current value held by the query optimizer. If the deviation is less than this threshold, no update statistics is actually performed when you select Execute.

The update level dialog box has the levels defaulted to high, medium and low respectively. For more information about update levels, see the Informix documentation. Unless you have very good knowledge of the Informix database, we recommend you leave them as they are:

heading index columns

This refers to the leading component of an index and is set to high because the query optimizer can best optimize queries with exact information on such columns. You cannot change this parameter.

non heading index columns

This refers to the secondary component of an index and is defaulted to medium because the query optimizer requires fairly accurate information about such columns in order to optimize queries.

non index columns

This refers to columns that are not part of any index and is defaulted to no update because the query optimizer does not need accurate information about such columns in order to optimize queries.

Choose Execute and you see a message to confirm that SAPDBA is running the update. Then you see a message to confirm satisfactory completion:

Update statistics for table ‘state’ was successful!

See also:

Informix documentation