SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
U-8
Considerations—UPDATE STATISTICS
The PROBABILISTIC algorithm is designed to give more accurate results than the
previous (SIMPLE) algorithm. Moreover, when you specify the PROBABILISTIC
option, SQL computes statistics in parallel on partitioned tables; SQL operates in
parallel on each partition in a table.
If you specify the PROBABILISTIC option, SQL ignores the EXACT and SAMPLE
n BLOCKS options. With the PROBABILISTIC algorithm, SQL always reads every
row in the table. If you specify the SIMPLE option, SQL uses either the EXACT or
the SAMPLE n BLOCKS option, depending on which you specify.
The default option is the SIMPLE algorithm.
In future product version updates (PVUs) of NonStop SQL/MP, SQL might switch to
the PROBABILISTIC option as the default option. When this change is
incorporated, an UPDATE STATISTICS statement that has no options specified will
no longer use the SIMPLE algorithm, but instead use the PROBABILISTIC
algorithm. At present, if you want to use the PROBABILISTIC algorithm, you must
specify the PROBABILISTIC option in your UPDATE STATISTICS statements in
embedded SQL or OBEY command files.
EXACT | SAMPLE n BLOCKS
specifies whether to compute statistics by reading each row in each partition of the
table (EXACT) or to compute statistics by sampling n blocks of each partition in the
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

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. For more information, see
Concurrency on page C-65.

UPDATE STATISTICS collects and saves these statistics:

Date and time UPDATE STATISTICS was last run on the table

Number of rows in the table