For the syntax of the UPDATE STATISTICS statement and for more information about histogram tables, see the SQL/MX Reference Manual.
Updating Histogram Statistics
When you update statistics on a column or a group of columns, NonStop SQL/MX generates histogram statistics. Histogram statistics enable the optimizer to create efficient access plans.
Histogram statistics are stored in two tables. The name and location of these tables depends on whether you are using SQL/MX or SQL/MP tables.
Table 3-1 shows the temporary tables information created for histograms.
Note. The compiler uses default statistics if no updated statistics exist for the table and column. When the compiler uses default statistics, the execution plan provided might not be the optimal plan. If statistics have not been updated, the compiler uses the block count from the file label. However, if the block count is zero, the compiler uses the default value for the HIST_NO_STATS_ROWCOUNT attribute. The compiler issues warnings if statistics have not been generated for a relevant column when the table contains more rows than the value defined by the default HIST_ROWCOUNT_REQUIRING_STATS.
Table 3-1. Histogram Temporary 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
as the primary partition, in the ZZMXTEMP subvolume. File names catalog.schema.
SQLMX_<object_uid_of_table name>_<seconds_part_from_c urrent_timestamp>_<microse conds_part_from_current_ti mestamp> \node.$vol.ZZMXTEMP. tablename
Size Limits Files are always format 2, limited to 1 TB or the amount of available space on the disk volume.
File format is determined by the format of the base table’s primary partition.
Format 1: The temporary table is limited to 2 GB.
Format 2: The temporary table is limited to 1 TB of the space available on the disk volume.
Keeping Statistics Current
HP NonStop SQL/MX Query Guide —640323-001 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, NonStop 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 NonStop 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, NonStop 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
Keeping Statistics Current
HP NonStop SQL/MX Query Guide —640323-001 3 -4
Updating Histogram Statistics
In Case 2, the index (E) is defined as nonunique, so the KEY is added to the end of the INDEX, and the index is INDEX+KEY:
Case 2
KEY: (A, B, C) => (A, B, C), (A, B)
INDEX: (E) nonunique => (E, A, B, C), (E, A, B), (E, A) RESULT: (A, B, C), (A, B), (E, A, B, C), (E, A, B), (E, A)
In Case 3, because the index (E) is defined as a unique index, the KEY is not added to the end of the INDEX. The index is INDEX. Because the index is a single column, it is processed as a single-column histogram only (no multicolumn histograms are
generated).
Case 3
KEY: (A, B, C) => (A, B, C), (A, B)
INDEX: (E) unique = > no multicolumn histogram RESULT: (A, B, C), (A, B)
Histogram tables are not automatically updated when you update a table for which statistics are stored. To keep the histogram statistics current, execute the UPDATE STATISTICS statement after significantly updating tables.
NonStop SQL/MX reduces compile time for less complex queries by caching
histograms. When the histogram is cached, it can be retrieved from the cache rather than from the disk for future queries on the same table. Histogram caching provides faster access to histograms.
Several default settings for histogram tables can be changed. For more information, see the SYSTEM_DEFAULTS table entry in the SQL/MX Reference Manual.