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

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—540440-003
2-241
UPDATE STATISTICS Statement
For more information about specifying columns, see Generating and Clearing
Statistics for Columns on page 2-244.
EVERY COLUMN
The EVERY COLUMN keyword indicates that histogram statistics are to be
generated for each individual column of table and any multicolumns that
make up the primary key and indexes. For example, table has columns A, B,
C, D defined, where A, B, C compose the primary key. In this case, the ON
EVERY COLUMN option generates a single column histogram for columns A,
B, C, D, and two multicolumn histograms of (A, B, C) and (A, B).
The EVERY COLUMN option does what EVERY KEY does, with additional
statistics on the individual columns.
EVERY KEY
The EVERY KEY keyword indicates that histogram statistics are to be
generated for columns that make up the primary key and indexes. For
example, table has columns A, B, C, D defined. If the primary key comprises
columns A, B, statistics are generated for (A, B), A and B. If the primary key
comprises columns A, B, C, statistics are generated for (A,B,C), (A,B), A, B, C.
If the primary key comprises columns A, B, C, D, statistics are generated for
(A, B, C, D), (A, B, C), (A, B), and A, B, C, D.
histogram-option
GENERATE n INTERVALS
is an optional clause that specifies histograms are to be generated with
approximately n number of intervals. The actual number of generated
intervals might be more or less than the number n. Depending on the
table’s size and data distribution, each histogram should contain n
intervals. NonStop SQL/MX attempts to distribute the rows evenly given
the number of intervals.
The number n of intervals must be an integer between 1 and 200
(1 < n < 200). The interval number that you set is used for all column
groups.
If you do not specify the number of intervals, a system default value is
automatically provided based on the table size and other factors. It is
recommended that you allow the system to determine the optimal number
of intervals.
SAMPLE [sample-option] [SET ROWCOUNT c ]
is an optional clause that specifies that sampling is to be used to gather a
subset of the data from the table. UPDATE STATISTICS uses a temporary
table to store the sample results and generates histograms. See Histogram
Table Properties on page 10-71 for details.