SQL/MP Query Guide

Improving Query Performance With Environmental
Options
HP NonStop SQL/MP Query Guide—524488-003
4-3
Keeping Statistics Current
•
Always specify the NO RECOMPILE option when using UPDATE STATISTICS, for
this reasons:
°
By default, an UPDATE STATISTICS operation invalidates dependent
programs, even if UPDATE STATISTICS is executed within a transaction that is
backed out.
Catalogs are audited; program file labels are not. Because program file labels
are not audited, updates to program file labels are not backed out.
Consequently, if a transaction is backed out, the program file labels are left in
an invalid state, while the catalog specifies a valid state.
°
To avoid invalidating dependent programs and therefore avoid inconsistencies
between the program file label and the catalog. Until you explicitly compile the
affected programs, however, they will not use the new statistics.
•
If you want to preserve the existing query execution plan, please be aware that
running UPDATE STATISTICS might cause the optimizer to choose a different
plan.
•
Run UPDATE STATISTICS after creating a new index for a table; otherwise, SQL
returns a warning for subsequent operations on the table.
For a thorough evaluation of access options, include key columns, index columns, and
those nonindex columns that participate in predicates. To update statistics for all
columns, you must specify UPDATE ALL STATISTICS.
This example updates statistics for primary key columns of the EMPLOYEE table and
columns that have been specified in any alternate index on the table:
UPDATE STATISTICS FOR TABLE EMPLOYEE NO RECOMPILE;
This example requests statistics by reading all rows in the first 50 blocks of each
partition of the EMPLOYEE file:
UPDATE STATISTICS FOR TABLE EMPLOYEE SAMPLE 50 BLOCKS;
You can choose to read the entire table (EXACT option) or a specified number of
blocks of each partition (SAMPLE n BLOCKS option) for computing statistics. These
options help control the amount of time spent calculating statistics. If neither of these
options is specified, statistics are collected by reading all rows in partitions smaller than
1,000 blocks, and approximately 500 blocks from each partition larger than 1,000
blocks.
Statistics are collected at the table level, except for row count and nonempty block
count, which are stored on a partition-by-partition basis. Unique entry count is divided
equally among the partitions of a table, with any remainder added to the primary
partition.
For more information about the UPDATE STATISTICS statement, see the SQL/MP
Reference Manual and the SQL/MP Installation and Management Guide. For
information on using FILEINFO and FUP RELOAD, see the SQL/MP Installation and
Management Guide.