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

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—544517-008
2-270
Examples of UPDATE STATISTICS
When you specify a sample-table-name, you must have ALL privileges on the
temporary table and must own its schema or be the super ID.
For more information on partitioned temporary tables, see the SQL/MX Query Guide.
When a SAMPLE clause is specified, the UPDATE STATISTICS command executes a
SELECT statement with a corresponding SAMPLE clause and inserts the records into
the temporary table. The sampling operation can be performed by either the SQL/MX
Executor or the DP2. You can control this operation by using the
ALLOW_DP2_ROW_SAMPLING default attribute. For more information on this
attribute, see Default Attributes on page 10-33.
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-81.
•
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.
Note. The USING SAMPLE TABLE clause is not supported with SQL/MP tables.