SQL/MX 2.x Reference Manual (H06.10+, J06.03+)

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—544517-008
2-269
Considerations for UPDATE STATISTICS
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 2
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-46.
Using Sample Table with Partitions
While updating the statistics for SQL/MX tables, you can partition the temporary tables
used by the UPDATE STATISTICS command. Use the USING SAMPLE TABLE WITH
PARTITIONS clause to create a partitioned temporary table. When this clause is used,
the temporary table is partitioned the same way as the base table for which the
statistics are updated.
You can also create your own temporary table and specify it for the UPDATE
STATISTICS command by using the USING SAMPLE TABLE sample-table-name
clause. You can use this option to create a temporary table that has a different
partitioning scheme from the default one. This includes:
•
changing the number and nature of the partitions
•
changing the key ranges
•
controlling the disk layout
The table represented by sample-table-name must be a SQL/MX table. It must
have the same column attributes as the base table—the columns must match in
number, order, and data type. The table should not have any indexes, triggers, or
constraints and it must be empty.