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

Metadata Tables
HP NonStop SQL/MX Reference Manual—540440-003
10-70
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-76.
Creating Histogram Tables
When you execute the UPDATE STATISTICS statement for a table in your database,
NonStop SQL/MX automatically creates histogram tables if they do not already exist for
that table: HISTOGRAMS and HISTOGRAM_INTERVALS for SQL/MX tables,
HISTINTS and HISTOGRM for SQL/MP. For NonStop SQL/MP, these tables are
registered in the catalog of the primary partition of the user table you specify in the
UPDATE STATISTICS statement.
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