HP 3000 Manuals

ALLBASE/SQL Reference Manual

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

MPE/iX 5.5 Documentation