SQL/MX 3.x Query Guide (H06.22+, J06.11+)

Keeping Statistics Current
HP NonStop SQL/MX Query Guide—640323-001
3-6
Sampling and UPDATE STATISTICS
created in the default volume specified by the _DEFAULTS define. For SQL/MP tables,
a single partition is created on the volume specified in the HIST_SCRATCH_VOL
default attribute. If the default attribute is not used, the temporary table is created in the
same volume as the primary partition of the table. Because the temporary table is used
for data gathering and calculation purposes, you must have adequate disk space to
accommodate it on your system. For more information about the
HIST_SCRATCH_VOL default attribute, see the SQL/MX Reference Manual.
Starting with SQL/MX Release 2.3.2, the temporary table can be created as a
partitioned table. When you use the USING SAMPLE TABLE WITH PARTITIONS
clause, SQL/MX creates a temporary table that is partitioned the same way as the
base table for which the statistics are collected. The partitioned temporary table can
increase the speed of the UPDATE STATISTICS command significantly because both
write and read operations on the table can occur simultaneously.
Use a partitioned temporary table when the UPDATE STATISTICS command takes
several tens of minutes to complete—there might not be any gain when the command
takes only a few minutes.
The performance with partitioned temporary tables is heavily dependent on the degree
of parallelism in the operations that the UPDATE STATISTICS command uses to write
to and read from the temporary table. The following steps are recommended for
obtaining the maximum parallelism from SQL/MX:
•
Ensure that the number of partitions is a multiple of the number of CPUs in the
system.
•
Distribute the data records evenly across all partitions.
•
Distribute the partitions evenly across all available disks.
•
Distribute the disks evenly across all available CPUs.
An uneven distribution might degrade the performance with partitioned temporary
tables. You can resolve this problem, in some cases, by creating a temporary table and
specifying it for the command to use. To do this, use the USING SAMPLE TABLE
table-name clause. The temporary table must meet these criteria:
•
The column attributes must be the same as the base table—the number of
columns, order, and data types must match.
•
There must not be any indexes or triggers.
•
There must be no constraints.
•
The table must be empty.
When partitioning the temporary table, avoid using the disks and CPUs that are heavily
loaded and try to distribute the partitions evenly across the remaining disks and CPUs.
When the USING SAMPLE TABLE clause is used, the HIST_SCRATCH_VOL default
attribute will be ignored.