SQL/MX 2.x Reference Manual (H06.04+)

Metadata Tables
HP NonStop SQL/MX Reference Manual—540440-003
10-71
Creating Histogram Tables
reside in the same HISTOGRAMS and HISTOGRAM_INTERVAL or HISTINTS and
HISTOGRM tables.
You can specify the number of intervals for the table statistics in the UPDATE
STATISTICS statement. If you do not specify the number of intervals,
NonStop SQL/MX provides a default number based on the table size and other factors.
The histogram tables are not automatically updated when you alter a table for which
statistics are stored. Therefore, after you alter a table, you should execute UPDATE
STATISTICS again for the table to keep its histogram statistics current.
If you drop an SQL/MP user table with DROP TABLE, the obsolete histograms for that
table are not immediately deleted in the histogram tables. You can use the CLEAR
option in UPDATE STATISTICS to delete all histograms for that table before you drop
the table. See UPDATE STATISTICS Statement on page 2-239. Obsolete rows in
SQL/MX histogram tables are automatically deleted.
Before you update statistics you can estimate the size of SQL/MX and SQL/MP
temporary tables, in rows, based on the base tables size in rows. For example, if the
base table has 12 million rows and you request a 2% sample, the temporary table will
have approximately 240,000 rows.
Histogram Table Properties
SQL/MX Objects SQL/MP Objects
Histogram
tables
Registered in the same
catalog.schema as the table.
Registered in the catalog of the primary
partition of the table.
Located in the same
catalog.schema as the table.
Located in the same \node.$vol.subvol as
the catalog.
File names:
catalog.schema.HISTOGRAMS
catalog.schema.HISTOGRAM
_INTERVALS
File names:
\node.$vol.subvol.HISTOGRM
\node.$vol.subvol.HISTINTS
Temporary
tables
Registered in the same
catalog.schema as the table.
Registered in the catalog of the primary
partition of the table.
Located in a volume randomly
chosen by NonStop SQL/MX from
the list specified by
HIST_SCRATCH_VOL, or in the
volume specified in the
_DEFAULTS define.
If multiple volumes are specified
with HIST_SCRATCH_VOL and
the table does not contain a
system generated key (SYSKEY),
hash partitioning is performed over
all specified volumes.
May be audited or non-audited.
Located in a volume randomly chosen by
NonStop SQL/MP from the list specified
by HIST_SCRATCH_VOL, or in the same
\node.$vol as the primary partition, in the
ZZMXTEMP subvolume.
Single partition.
May be audited or non-audited.