SQL/MX 3.2 Query Guide (H06.25+, J06.14+)

Keeping Statistics Current
HP NonStop SQL/MX Release 3.2 Query Guide—663851-002
3-2
Updating Histogram Statistics
For the syntax of the UPDATE STATISTICS statement and for more information about
histogram tables, see the SQL/MX Reference Manual.
Updating Histogram Statistics
When you update statistics on a column or a group of columns, NonStop SQL/MX
generates histogram statistics. Histogram statistics enable the optimizer to create
efficient access plans.
Histogram statistics are stored in two tables. The name and location of these tables
depends on whether you are using SQL/MX or SQL/MP tables.
Table 3-1
shows the temporary tables information created for histograms.
Note. The compiler uses default statistics if no updated statistics exist for the table and
column. When the compiler uses default statistics, the execution plan provided might not be
the optimal plan. If statistics have not been updated, the compiler uses the block count from
the file label. However, if the block count is zero, the compiler uses the default value for the
HIST_NO_STATS_ROWCOUNT attribute. The compiler issues warnings if statistics have not
been generated for a relevant column when the table contains more rows than the value
defined by the default HIST_ROWCOUNT_REQUIRING_STATS.
Table 3-1. Histogram Temporary Tables
Statistics SQL/MX Objects SQL/MP Objects
Registration Registered in the same
catalog.schema as the table.
Registered in the catalog of the
primary partition of the table.
Location Located in the same
catalog.schema as the table.
Located in the same \node.$vol
as the primary partition, in the
ZZMXTEMP subvolume.
File names catalog.schema.
SQLMX_<object_uid_of_table
name>_<seconds_part_from_c
urrent_timestamp>_<microse
conds_part_from_current_ti
mestamp>
\node.$vol.ZZMXTEMP.
tablename
Size Limits Files are always format 2, limited to
1 TB or the amount of available
space on the disk volume.
File format is determined by the
format of the base table’s primary
partition.
Format 1: The temporary table is
limited to 2 GB.
Format 2: The temporary table is
limited to 1 TB of the space available
on the disk volume.
TP663851.fm Page 2 Thursday, August 2, 2012 4:47 PM