SQL/MX 2.x Reference Manual (H06.10+, J06.03+)

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—544517-008
2-268
Considerations for UPDATE STATISTICS
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-81
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.
Sample Option
When you use the SAMPLE option, the UPDATE STATISTICS statement estimates the
unique entry counts for each column for each histogram interval. The estimated unique
entry counts tend to be more accurate for those columns that have fewer unique
entries than the sampled number of rows. However, for columns that have been
defined with the UNIQUE constraint, the unique entry count is always equal to the row
count, regardless of the sample size. For more information about the unique entry
count, see SAMPLE Clause
on page 7-8.
If you specify the ROWCOUNT clause, use a value for c equal to the number of rows
in the table. If you use a value that is less than or greater than the number of rows,
results will not be accurate.
Sampling of Large Tables
Use the SAMPLE clause to reduce the run time for updating statistics of large tables:
•
For tables with more than 2 million rows, use:
UPDATE STATISTICS FOR TABLE big_table ON EVERY COLUMN
SAMPLE SET ROWCOUNT rowcount_big_table;