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

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—544517-008
2-266
Considerations for UPDATE STATISTICS
SET ROWCOUNT c
is an optional clause that specifies the number of rows in the table. The value c
must be an integer that is greater than or equal to zero (c > 0).
If the ROWCOUNT clause in not specified, NonStop SQL/MX determines the
number of rows in the table either by estimation or SELECT COUNT(*).
See SAMPLE Clause on page 7-8.
sample-table-clause
USING SAMPLE TABLE WITH PARTITIONS
directs SQL/MX to partition the temporary table. The temporary table is
partitioned the same way as the base table on which the UPDATE
STATISTICS command is run.
USING SAMPLE TABLE sample-table-name
directs SQL/MX to use the table specified by sample-table-name as the
temporary table.
Considerations for UPDATE STATISTICS
Physical Statistics
Physical statistics (index level, nonempty block count, and EOF) are generated for
UPDATE STATISTICS statements unless you use the CLEAR option.
Using Statistics
Use UPDATE STATISTICS to collect and save statistics on columns. The SQL
compiler uses histogram statistics to determine the selectivity of predicates, indexes,
and tables. Because selectivity directly influences the cost of access plans, regular
collection of statistics increases the likelihood that NonStop SQL/MX will choose
efficient access plans.
When a user table is changed, either by changing its data significantly or its definition,
reexecute the UPDATE STATISTICS statement for the table.
Authorization and Locking
To run the UPDATE STATISTICS statement against SQL/MX tables, you must have the
authority to read the user table for which statistics are generated. To run the UPDATE
STATISTICS statement against SQL/MP tables, you must own the two histogram
Note. The sample-table-clause is supported only for SQL/MX tables. It cannot be
used with SQL/MP tables.