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

Keeping Statistics Current
HP NonStop SQL/MX Query Guide—523728-003
3-8
Testing the Results for SQL/MX Tables
b. In MXCI, issue the UPDATE STATISTICS command for required column
groups.
c. In MXCI, recompile the query.
d. In MXCI, use EXPLAIN to review the cost information for your query.
e. If necessary, use the UPDATE STATISTICS CLEAR option (in MXCI) to
remove histograms for unwanted column groups.
f. If necessary, in SQLCI, restore backup histogram tables:
SQLCI> DROP TABLE histogrm;
SQLCI> DROP TABLE histints;
SQLCI> DUP myogrm, histogrm;
SQLCI> DUP myints, histints;
Testing the Results for SQL/MX 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 MXCI, back up current histogram tables, if any:
> CREATE TABLE myhist LIKE HISTOGRAMS;
> INSERT INTO myhist SELECT * FROM HISTOGRAMS;
> CREATE TABLE myhistint LIKE HISTOGRAM_INTERVALS;
> INSERT INTO myhistint SELECT * FROM HISTOGRAM_INTERVALS;
b. Issue the UPDATE STATISTICS command for required column groups.
c. Recompile the query.
d. Use EXPLAIN to review the cost information for your query.
e. If necessary, use the UPDATE STATISTICS CLEAR option to remove
histograms for unwanted column groups.