SQL/MX 2.x Reference Manual (G06.24+, H06.03+)

Metadata Tables
HP NonStop SQL/MX Reference Manual—523725-004
10-69
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-245. Obsolete rows in
SQL/MX histogram tables are automatically deleted.
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 the same
catalog.schema as the table.
Located in the volume specified by
HIST_SCRATCH_VOL, or in the
volume specified in the
_DEFAULTS define.
Located in the volume specified by
HIST_SCRATCH_VOL, or in the same
\node.$vol as the primary partition, in the
ZZMXTEMP subvolume.
File name:
catalog.schema.SQLMX
_tablename
File name:
\node.$vol.ZZMXTEMP.tablename
Size limits:
Because files are always format 2,
the temporary table is limited to 1
TB or the amount of available
space on the disk volume.
Size limits:
File format is determined by format of the
base table’s primary partition.
If it is format 1, the temporary table is
limited to 2 GB.
If it is format 2, the temporary table is
limited to 1 TB or the amount of available
space on the disk volume.