SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—540440-003
2-246
Examples of UPDATE STATISTICS
Managing SQL/MP Histograms
Before you drop an SQL/MP table, perform UPDATE STATISTICS with the CLEAR
option. Otherwise, orphan histograms for that table are left on the system. However, if
you drop an SQL/MP table before performing this step, use UPDATE STATISTICS with
the CLEAR option to remove orphan tables:
1. Create a dummy table in the catalog where the primary partition of the table you
dropped resided:
CREATE TABLE trash (a int);
2. Run UPDATE STATISTICS with the CLEAR option:
UPDATE STATISTICS FOR TABLE trash CLEAR;
The CLEAR option directs NonStop SQL/MX to remove histograms for table
trash, and any orphaned histograms.
3. Drop the dummy table:
DROP TABLE trash;
Histograms for SQL/MX tables are automatically deleted when the table is dropped.
Examples of UPDATE STATISTICS
For examples of histogram data, see Examples of Histogram Tables on page 10-76.
•
This example generates four histograms for the columns jobcode, empnum,
deptnum, and (empnum, deptnum) for the table EMPLOYEE. Depending on the
table’s size and data distribution, each histogram should contain 10 intervals.
UPDATE STATISTICS FOR TABLE employee
ON (jobcode),(empnum, deptnum)
GENERATE 10 INTERVALS;
--- SQL operation complete.
•
This example generates histogram statistics using the ON EVERY COLUMN
option for the table DEPT. This statement performs a full scan, and the
NonStop SQL/MX determines the default number of intervals.
UPDATE STATISTICS FOR TABLE dept
ON EVERY COLUMN;
--- SQL operation complete.
•
This example generates statistics for a sample from table MAILINGS. The sample
size is 7.3529 percent, and the number of rows in the table is 272,000.
UPDATE STATISTICS FOR TABLE mailings
ON EVERY COLUMN
SAMPLE RANDOM 7.3529 PERCENT CLUSTERS OF 1 BLOCKS
SET ROWCOUNT 272000;