 |
» |
|
|
|
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
|
|
