SQL/MX 3.2.1 Reference Manual (H06.26+, J06.15+)

SQL/MX Statements
HP NonStop SQL/MX Release 3.2.1 Reference Manual—691117-005
2-409
Considerations for 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.
If UPDATE STATISTICS statement fails with unique constraint violation error, then the
control query default USTAT_AVOID_DUPLICATE_ROWS_WHEN_SAMPLING must
be set to 'ON' and the UPDATE STATISTICS statement must be rerun. This default
attribute is available from SQL/MX H32^APY SPRs onwards.
For more information on this attribute, see Default Attributes on page 10-39.
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.
Note. The USING SAMPLE TABLE clause is not supported with SQL/MP tables.