Manualshelf

SQL/MX 2.x Query Guide (G06.24+, H06.03+)

ManualsBrandsHP ManualsServerHP NonStop G-Series
51525354555657585960
Keeping Statistics Current
HP NonStop SQL/MX Query Guide—523728-003
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, 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_tablename
\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 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 amount of
available space on the disk volume.