SQL/MX 2.x Query Guide (G06.24+, H06.03+)

HP NonStop SQL/MX Query Guide—523728-003
3-1
3 Keeping Statistics Current
When you update statistics, information about a table is updated in the histogram
tables so that the information more accurately represents the current content and
structure of the database. Use the information in this section to understand when and
why you should update statistics:
•
Histogram Statistics on page 3-1
•
Sampling and UPDATE STATISTICS on page 3-5
•
Testing the Results of UPDATE STATISTICS on page 3-7
You must initiate the UPDATE STATISTICS statement. SQL/MX does not
automatically update statistics.
Histogram Statistics
Histograms are critical to the optimizer’s ability to differentiate between plans. The
optimizer uses histograms to estimate the row count that flows out of each operator in
a query execution plan and uses these estimates to calculate the total cost of a plan.
Different plans are then compared to find the best plan.
The best practice is to update statistics for every table column involved in a query (that
is, every column in every predicate, join, group by, and order by expression). However,
you might decide that the cost of updating statistics is not justified by the gain in
optimizer accuracy. In this case, you can adjust the default setting constant values for
histograms. For information about histogram default settings, see the
SYSTEM_DEFAULTS Table entry of the SQL/MX Reference Manual.
When you update statistics, histogram statistics for a group of columns or individual
columns are collected, including for each column or set of columns:
•
Current number of rows in the table
•
Number of unique entries in the table
•
Highest value for the table column
•
Lowest value for the table column
•
Number of intervals in the histogram
•
Number of unique entry counts (UEC) in each interval
•
Number of rows in each interval
•
Interval boundary
Histogram statistics are updated by using the UPDATE STATISTICS statement with
the ON clause. Stored in the user catalog, histogram statistics are stored for the entire
table and columns in the HISTOGRAM and HISTOGRAM_INTERVALS user metatdata
tables. Histogram statistics are discussed under Updating Histogram Statistics on
page 3-2.