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

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—540440-003
2-247
Examples of UPDATE STATISTICS
•
Suppose that a construction company has an ADDRESS table of potential sites
and a DEMOLITION_SITES table that contains some of the columns of the
ADDRESS table. The primary key is ZIP. Join these two tables on two of the
columns in common:
SELECT COUNT(AD.number), AD.street,
AD.city, AD.zip, AD.state
FROM address AD, demolition_sites DS
WHERE AD.zip = DS.zip AND AD.type = DS.type
GROUP BY AD.street, AD.city, AD.zip, AD.state;
To generate statistics specific to this query, enter these statements:
UPDATE STATISTICS FOR TABLE address
ON (street), (city), (state), (zip, type);
UPDATE STATISTICS FOR TABLE demolition_sites
ON (zip, type);
•
This example removes all histograms for table DEMOLITION_SITES:
UPDATE STATISTICS FOR TABLE demolition_sites CLEAR;
•
This example selectively removes histograms for column STREET in table
ADDRESS:
UPDATE STATISTICS FOR TABLE address ON street CLEAR;
For additional examples, see the SQL/MX Query Guide.