SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
U-10
Examples—UPDATE STATISTICS
SQLCI LOAD or FUP RELOAD to reorganize the table before updating the
statistics.

UPDATE STATISTICS momentarily locks the definition of the table in the catalog
during the operation, but not the table itself; the statement uses BROWSE
ACCESS.

All partitions of a table must be available for SQL to generate accurate statistics,
not just the partition specified in the UPDATE STATISTICS statement.
If any partitions are unavailable when you run UPDATE STATISTICS, SQL
issues a warning, skips the unavailable partitions, and updates statistics for the
remaining partitions using default values for the unavailable partitions. If this
occurs, you should rerun UPDATE STATISTICS with all partitions as soon as
possible.
If a partitioned table does not have statistics (that is, if you have never
executed UPDATE STATISTICS for that table) and one of the partitions is not
available when you execute SELECT or another DML operation on the table,
SQL returns a warning and a file-system error even if the query does not
retrieve any rows from the unavailable partition. The warning does not occur
for very small tables, but you can prevent it from occurring at all by executing
UPDATE STATISTICS at least once for any partitioned table.

When your table has many partitions—for example, 100—you might want to avoid
putting the UPDATE STATISTICS statement in a user-defined TMF transaction.
With many partitions, the UPDATE STATISTICS operation might take so long that
TMF might have too little log file space to perform all the logging required by other
TMF transactions at the time.
If no user-defined TMF transaction is in progress when UPDATE STATISTICS
executes, SQL starts several for the operation but does not include scanning the
table for information within a transaction. Because UPDATE STATISTICS uses
BROWSE ACCESS to scan the table, the results are approximate.

To see current statistics, use the SELECT statement to retrieve the information
from the catalog tables. For example, the second-highest value of a column is
stored in the COLUMNS table. The STATISTICSTIME column of the BASETABS
table contains the time that statistics for the table were last updated, stored as a
timestamp in Greenwich mean time.
For more information about the information stored in the catalog, see CATALOGS
Table on page C-11 or the entry for a specific catalog table.
Examples—UPDATE STATISTICS

This example updates all statistics for a table named PARTLOC on the current
default volume and subvolume without invalidating the associated programs:
UPDATE ALL STATISTICS FOR TABLE PARTLOC NO RECOMPILE;