SQL/MX 2.x Reference Manual (G06.24+, H06.03+)

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—523725-004
2-251
Considerations for UPDATE STATISTICS
Sample Option
When you use the SAMPLE option, the UPDATE STATISTICS statement estimates the
unique entry counts for each column for each histogram interval. The estimated unique
entry counts tend to be more accurate for those columns that have fewer unique
entries than the sampled number of rows. However, for columns that have been
defined with the UNIQUE constraint, the unique entry count is always equal to the row
count, regardless of the sample size. For information about the unique entry count,
SAMPLE Clause on page 7-8.
If you specify the ROWCOUNT clause, you should use a value for c equal to the
number of rows in the table. If you use a value that is less than or greater than the
number of rows, results will not be accurate.
Sampling of Large Tables
Use the SAMPLE clause to reduce the run time for updating statistics of large tables:
•
For tables with more than two million rows, use:
UPDATE STATISTICS FOR TABLE big_table ON EVERY COLUMN
SAMPLE SET ROWCOUNT rowcount_big_table;
You can also specify groups of columns. This command uses the system default
for sample size, which is 2 percent of the total number of rows in the table or two
million rows, whichever is less.
•
If big_table is highly skewed on certain columns (that is, a column has a large
variance in the percentage of individual unique values), specify the sample size to
be greater than the system default.
Suppose that big_table has 50 million rows and you want to sample 10 percent
of the rows. Use the command:
UPDATE STATISTICS FOR TABLE big_table ON EVERY COLUMN
SAMPLE RANDOM 10 PERCENT;
You can also specify groups of columns.
Temporary Tables
Use the HIST_SCRATCH_VOL control query default to set the physical volume for
UPDATE STATISTIC’s temporary tables.
If you do not set this value, NonStop SQL/MX uses the default volume specified by the
_DEFAULTS define and the current node for SQL/MX tables. If not specified, NonStop
SQL/MX uses the volume of the table’s primary partition for SQL/MP tables. The
volume must be in the same node as the location of the catalog of the primary partition.
See the description of HIST_SCRATCH_VOL on page 10-43.