Updating Statistics for One Table with SAPDBA (SAP Library - SAP Database Guide: Informix (BC-DB-INF-DBA))

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.

To perform update statistics in parallel, you must meet the requirements given in SAP Note 189399. See the working processes field below. SAP intends to automate the preparations required in future.

Procedure

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

Note

This is identical to the report that 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.

You see a screen with the following possibilities:

Field	Meaning
table name	The name for the table you want to process. If you are not sure which name you require, you can use a partial entry (for example, sta* ).
threshold value: deviation %	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.
logfile output	The method of storing logging information from a processing run of update statistics: Normal A single entry is written to the file. Extra large One entry is written to the file for each table processed.
working processes: number	The number of processes used by update statistics. If you specify more than 1, SAPDBA performs update statistics in parallel, but you must first meet certain prerequisites (see above).
update level	This dialog box has the levels defaulted to high and medium. 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.

Choose Execute.

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