UPDATE STATISTICS

The UPDATE STATISTICS statement updates the system catalog to reflect a table's current characteristics, such as the number of rows and average row size. ALLBASE/SQL uses these statistics to choose an optimal way to process a query.

Scope

ISQL or Application Programs

SQL Syntax

  UPDATE STATISTICS FOR TABLE {[Owner.]TableName 
                                SYSTEM.SystemViewName}

Parameters

[Owner.]TableName: identifies a table.
SYSTEM.SystemViewName: identifies a system view.

Description

The UPDATE STATISTICS statement affects specific columns in certain system catalog views:
View Name
Columns Affected
SYSTEM.DBEFILE
DBEFUPAGES
SYSTEM.DBEFILESET
DBEFSUPAGES
SYSTEM.COLUMN
AVGLEN
SYSTEM.INDEX
CCOUNT
NPAGES
SYSTEM.TABLE
AVGLEN
NPAGES
NROWS
USTIME
Any sections that reference a table named in the UPDATE STATISTICS statement are marked invalid, but are revalidated the next time they are executed or the VALIDATE statement is issued if access and authorization criteria are satisfied.
Use this statement sparingly before preprocessing, after creating an index, and after periods of heavy update activity. For more information, on the UPDATE STATISTICS statement, refer to the ALLBASE/SQL Performance Guidelines.
The only views this statement works for are system views. Refer to the ALLBASE/SQL Database Administration Guide for a description of the system views.
UPDATE STATISTICS cannot be used with pseudotables — SYSTEM.ACCOUNT, SYSTEM.CALL, SYSTEM.COUNTER, SYSTEM.TRANSACTION, and SYSTEM.USER.
You may find it convenient to use the VALIDATE statement after an UPDATE STATISTICS. If you issue both statements during a period of low activity for the DBEnvironment, the optimizer will have current statistics on which to base its calculations, with minimal performance degradation.

Authorization

You can issue this statement if you have OWNER authority for the table or if you have DBA authority.

Example

You issue this statement after periods of heavy data update activity in order to keep access paths optimal.

UPDATE STATISTICS FOR TABLE PurchDB.Orders