SQL/MX 3.2.1 Reference Manual (H06.26+, J06.15+)

Metadata Tables
HP NonStop SQL/MX Release 3.2.1 Reference Manual—691117-005
10-85
User Metadata Tables (UMD): Histogram Tables
User Metadata Tables (UMD): Histogram Tables
HISTOGRAMS Table
HISTOGRAM_INTERVALS Table
HISTOGRM Table
HISTINTS Table
Examples of Histogram Tables
A histogram is a representation of a relationship in which each value of some
dependent variable corresponds to a range of values of the associated independent
variable or variables. For example, a histogram might be a chart showing the number
of people in New York in various age ranges.
NonStop SQL/MX provides a method for generating histograms that show how data is
distributed with respect to a column or a group of columns within a table. The purpose
of generating these statistics is to enable the optimizer to create efficient access plans.
When generating a histogram for a table, NonStop SQL/MX divides the range of
specified column values of the table into some number of intervals, distributing the
rows evenly within these intervals. It computes statistics associated with each interval
and then uses the statistics to devise optimized plans.
You can use the statistics in the histogram tables as a basis for partitioning large
tables. For example, if the employee number (the EMPNUM column, which is the
primary key) in the EMPLOYEE table has a nonuniform distribution, use the histogram
statistics to divide the range of employee numbers into partitions that distribute rows
evenly. See Examples of Histogram Tables on page 10-92.
Creating Histogram Tables
NonStop SQL/MX automatically creates histogram tables when a schema is created:
HISTOGRAMS and HISTOGRAM_INTERVALS for SQL/MX tables. HISTINTS and
HISTOGRM SQL/MP tables are automatically created and registered in the catalog of
the primary partition of the user table you specify in the UPDATE STATISTICS
statement, if they do not already exist for that table.
Before you drop the SQL/MP catalog that contains the histogram tables, you must
explicitly drop both of the tables.
You cannot update statistics on system metadata tables, including tables residing in
the DEFINITION_SCHEMA, MXCS_SCHEMA, SYSTEM_DEFAULTS_SCHEMA, and
SYSTEM_SCHEMA.
Generating Histogram Statistics
The UPDATE STATISTICS statement generates logical (table and column level)
statistics that are stored in histogram user tables. To examine the current statistics, use
the SELECT statement. The histograms for user tables registered in the same catalog
reside in the same HISTOGRAMS and HISTOGRAM_INTERVAL or HISTINTS and
HISTOGRM tables.