SQL/MX 3.2 Query Guide (H06.25+, J06.14+)

Keeping Statistics Current
HP NonStop SQL/MX Release 3.2 Query Guide—663851-002
3-8
Collecting Statistics for Multiple Columns
These sampling options perform a full table scan prior to selecting the sample set:
SAMPLE RANDOM x PERCENT
SAMPLE PERIODIC x ROWS EVERY y ROWS
These sampling options do not perform a full table scan to determine the sample set.
In addition, the accuracy of all these examples is equivalent to SAMPLE RANDOM x
PERCENT CLUSTER OF y BLOCKS:
SAMPLE
SAMPLE r ROWS
SAMPLE SET ROWCOUNT c
SAMPLE r ROWS SET ROWCOUNT c
Collecting Statistics for Multiple Columns
Multicolumn unique entry count (UEC) is the UEC for a combination of columns. It is
the total number of unique combinations for the set of columns. The multicolumn UEC
enables the optimizer to give a better prediction of the number of rows and to provide
better plans. Specifically, the multicolumn UEC enables the optimizer to predict the
number of rows resulting from a grouping operation on multiple columns and in the
case of multicolumn joins.
If you have a set of columns you normally use for grouping (such as JOBCODE and
DEPTNUM), without multicolumn statistics, the optimizer multiplies the UECs of the
columns together to get the combined UEC. However, this result can be orders of
magnitude higher than the real number of combinations. Likewise for joins, if you have
two tables joined by two or more columns, a multicolumn histogram for the set of join
columns from each table gives a better result.
Testing the Results of UPDATE STATISTICS
Because updating statistics can have a significant impact on plan quality, you can try to
determine the benefits of the operation before you decide to update the histogram
tables.
Testing the Results for SQL/MP Tables
To test the results of updating statistics:
1. Prepare a sample query from your application. (Consider using a commonly used
query from your application.)
2. Execute the query.
3. Use EXPLAIN to obtain the cost information for your query. For information about
reviewing plans with the EXPLAIN function, see Displaying Selected Columns of
the Execution Plan on page 4-5.
TP663851.fm Page 8 Thursday, August 2, 2012 4:47 PM