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

Keeping Statistics Current
HP NonStop SQL/MX Query Guide—523728-003
3-7
Collecting Statistics for Multiple Columns
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. Use EXPLAIN to obtain the cost information for your query.
3. Determine the effect of the UPDATE STATISTICS statement and optionally back
out the generated histogram if necessary:
a. In SQLCI, back up current histogram tables, if any:
SQLCI> DUP histogrm, myogrm;
SQLCI> DUP histints, myints;