SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
U-9
Considerations—UPDATE STATISTICS

Byte address of the end-of-file of the table
(The number of bytes indicates the space used by the table.)

Percent of blocks that contain rows (nonempty blocks)

Number of index levels for the indexes on the table
(Each level represents a disk access operation required to retrieve data.)

Number of unique entries in each column
(All null entries count as one unique entry, just as with other values.)

Second highest value in each column
(Ignores null values; uses highest nonnull value if number of unique entries is
three or fewer.)

Second-lowest value in each column
(Ignores null values; uses lowest nonnull value if number of unique entries is
three or fewer.)
Except for row count, statistics are for the entire table (not for individual
partitions as in earlier PVUs 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.

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