NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
U-9
Considerations—UPDATE STATISTICS
table (SAMPLE n BLOCKS) and extrapolating from that sample. The value n must
be greater than zero.
If you do not specify either option, SQL computes statistics based on reading all
rows in partitions smaller than 1000 blocks and reading approximately 500 blocks
from partitions of 1000 blocks or more. (In the latter case, SQL reads a larger
sample if less than 3 percent of the total values have been sampled and 97 percent or
more of the sampled values are distinct.)
Considerations—UPDATE STATISTICS
•
Authorization requirements
To update statistics for a table, you must be the generalized owner of the table. You
must also have authority to read the table and to write to the catalogs that describe
the table.
Only one DDL statement can operate on a given SQL object (or partition of an SQL
object) at a time. An error occurs if you attempt to execute an UPDATE
STATISTICS statement while another process is executing a DDL operation on the
same object. The specific error depends on the DDL operation involved and the
phase of the operation at which the conflict occurs. (See Concurrency
on page C-60
for more information.)
•
Statistics collected
UPDATE STATISTICS collects and saves these statistics:
°
Date and time UPDATE STATISTICS was last run on the table
°
Number of rows in the table
°
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.)