NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
U-10
Considerations—UPDATE STATISTICS
Except for row count, statistics are for the entire table (not for individual partitions
as in earlier releases of NonStop SQL/MP). Note that the second-highest and
second-lowest values reflect the column values of the entire table, rather than of a
single partition. The unique entry count is the unique entry count for the entire table
divided by the number of partitions in the table.
UPDATE STATISTICS does not erase statistics already in the catalog that are
unaffected by the current update. For example, if an UPDATE STATISTICS
statement follows an UPDATE ALL STATISTICS statement for the same table, the
statistics remain unchanged for the columns that are not part of an index or the
primary key or clustering key.
When a table or a table partition is empty, UPDATE STATISTICS uses default
values to update items for which it does not have actual values and returns warning
1404. UPDATE STATISTICS always enters the current timestamp in the
STATISTICSTIME column of the BASETABS catalog table, whether or not other
statistics are available.
These statistics are used by the SQL compiler in selecting a strategy for an
execution plan. If the structure, contents, or indexes of the table have changed, or if
you experience a degradation in performance, use UPDATE STATISTICS to update
the information about the table in the catalog. You should also recompile your
programs. With up-to-date information, the SQL compiler can choose the best path
for queries on the table.
•
Preparation for UPDATE STATISTICS (fragmented tables)
Before executing UPDATE STATISTICS, determine whether the table is fragmented
(that is, whether the pages have a large amount of unused space). At the command
interpreter prompt, use the FILEINFO command with the STATISTICS option to
check the table. If the average percent of slack is large, use SQLCI LOAD or FUP
RELOAD to reorganize the table before updating the statistics.
•
Locking
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.
•
Use with partitioned tables
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