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

Keeping Statistics Current
HP NonStop SQL/MX Query Guide—523728-003
3-3
Updating Histogram Statistics
Table 3-2 compares histogram statistics table information.
The HISTOGRAMS and HISTOGRM tables store histogram-specific table and column
information. The HISTOGRAM_INTERVAL and HISTINTS tables store interval
information for the data distribution of a column or group of columns. When you run
UPDATE STATISTICS again for the same user table, the new data replaces the data
previously generated and stored in the histogram tables.
The HISTOGRAMS and HISTOGRM tables show how data is distributed with respect
to a column or a group of columns. When generating a histogram for a table, SQL/MX
distributes the values of the specified columns into some number of intervals. An
interval represents a range of values for the column. The range of values for each
interval is selected by SQL/MX so that every interval represents approximately the
same number of rows in the table.
For example, if a table contains 1 million rows and UPDATE STATISTICS generates
20 intervals for a column in that table, each interval represents 50,000 rows. (This is
sometimes known as “equal height” distribution over the histogram intervals.) The
optimizer computes statistics associated with each interval and uses the statistics to
devise optimized plans.
The ON EVERY COLUMN clause of the UPDATE STATISTICS statement generates
separate histogram statistics for every individual column and any multicolumns that
make up the primary key and indexes in the table.
Consider a table that contains columns A, B, C, D, and E. The ON EVERY COLUMN
option generates a single column histogram for columns A, B, C, D, E. The number of
multicolumn histograms generated depends on the primary key definition and the
indexes defined. With the primary key defined on A, B, and C and no index defined,
SQL/MX generates two multicolumn histograms (A, B, C) and (A, B). Using the same
table and columns, the next example shows when multicolumn histograms are
generated based on the primary key and defined indexes:
Table has columns A, B, C, D, E
Case 1
KEY: (A, B, C,) => (A, B, C), (A, B)
INDEX: none => none
RESULT: (A, B, C), (A, B)
Table 3-2. Histogram Statistics 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.subvol as the catalog.
File names catalog.schema.HISTOGRAMS
catalog.schema.
HISTOGRAM_INTERVALS
\node.$vol.subvol.HISTOGRM
\node.$vol.subvol.HISTINTS