SQL/MX 2.x Reference Manual (H06.10+, J06.03+)

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—544517-008
2-271
Examples of UPDATE STATISTICS
•
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;
•
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;
•
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. The records
that are selected by sampling are stored in a temporary table, which is partitioned
the same way as MAILINGS. The data in the temporary table is then used to
generate the statistics.
UPDATE STATISITCS FOR TABLE MAILINGS
ON EVERY COLUMN
SAMPLE RANDOM 7.3529 PERCENT CLUSTERS OF 1 BLOCKS
SET ROWCOUNT 272000
USING SAMPLE TABLE WITH PARTITIONS;