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

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—540440-003
2-244
Considerations for UPDATE STATISTICS
periods, and timeout errors do not occur. If the lock is held for a longer time, multiple
retry attempts help to complete concurrent operations with minimum timeout
interruption.
Generating and Clearing Statistics for Columns
To generate statistics for particular columns, name each column, or name the first and
last columns of a sequence of columns in the table. For example, suppose that a table
has consecutive columns CITY, STATE, ZIP. This list gives a few examples of possible
options you can specify:
The TO specification is useful when a table has many columns, and you want
histograms on a subset of columns. Do not confuse (CITY) TO (ZIP) with (CITY,
STATE, ZIP), which refers to a multicolumn histogram.
You can clear statistics in any combination of columns you specify, not necessarily with
the column-group-list you used to create statistics. However, those statistics will
remain until you clear them. For examples of SELECT statements to report on
statistics, see Examples of Histogram Tables on page 10-76
Column Lists and Access Plans
Generate statistics for columns most often used in data access plans for a table—that
is, the primary key, indexes defined on the table, and any other columns frequently
referenced in predicates in WHERE or GROUP BY clauses of queries issued on the
table. Use the EVERY COLUMN option to:
•
generate histograms for every individual column or multicolumns that make up the
primary key and indexes
•
enable the optimizer to choose a better plan.
The EVERY KEY option generates histograms that make up the primary key and
indexes.
If you often perform a GROUP BY over specific columns in a table, use multicolumn
lists in the UPDATE STATISTICS statement (consisting of the columns in the GROUP
BY clause) to generate histogram statistics that enable the optimizer to choose a better
plan. Similarly, when a query joins two tables by two or more columns, multicolumn
lists (consisting of the columns being joined) help the optimizer choose a better plan.
Single-Column Group Single-Column Group
Within Parentheses
Multicolumn Group
ON CITY, STATE, ZIP ON (CITY),(STATE),(ZIP) ON (CITY, STATE) or
ON (CITY,STATE,ZIP)
ON CITY TO ZIP ON (CITY) TO (ZIP)
ON ZIP TO CITY ON (ZIP) TO (CITY)
ON CITY, STATE TO ZIP ON (CITY), (STATE) TO (ZIP)
ON CITY TO STATE, ZIP ON (CITY) TO (STATE), (ZIP)