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

Keeping Statistics Current
HP NonStop SQL/MX Query Guide—523728-003
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.
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.
Knowing When to Update Statistics
Before you update statistics, consider:
•
Using sampling to reduce the amount of time required for updating statistics. See
Sampling and UPDATE STATISTICS on page 3-5.
•
Updating statistics only after a table has been loaded with data.
Other performance issues to consider when you experience reduced response time
are:
•
The node containing the table might have heavy disk usage because of long ad
hoc queries or reports.
•
If the table or index is distributed, the network might be rerouted or might have
heavy use.
•
SQL/MX does not automatically recompile your programs when you update
statistics.