|
|
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.
ISQL or Application Programs
UPDATE STATISTICS FOR TABLE {[Owner.]TableName
SYSTEM.SystemViewName}
- [Owner.]TableName
identifies a table.
- SYSTEM.SystemViewName
identifies a system view.
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.
You can issue this statement if you have OWNER authority for the table or if
you have DBA authority.
You issue this statement after periods of heavy data update
activity in order to keep access paths optimal.
UPDATE STATISTICS FOR TABLE PurchDB.Orders
|