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

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—540440-003
2-243
Considerations for UPDATE STATISTICS
See SAMPLE Clause on page 7-8.
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
tables, or be the super ID, and have the authority to read the user table for which
statistics are generated.
Because the histogram tables are registered in the schema (for SQL/MX tables) or
catalog (for SQL/MP) of the primary partition of table, you must have the authority to
read and write to this schema or catalog. Then, when the two histogram tables are
created, you become the owner of the tables. See User Metadata Tables (UMD):
Histogram Tables on page 10-70.
UPDATE STATISTICS momentarily locks the definition of the user table in the catalog
during the operation but not the user table itself. The UPDATE STATISTICS statement
uses READ UNCOMMITTED for the user table.
Transactions
Do not start a transaction before executing UPDATE STATISTICS because UPDATE
STATISTICS runs under that transaction. The TMF auto abort time could be exceeded
during the processing.
If you do not start a transaction for UPDATE STATISTICS, NonStop SQL/MX runs
multiple transactions, breaking down the long transaction.
If the SQL/MP metadata files are locked, UPDATE STATISTICS tries three times to
access them before reporting an error. Usually, metadata files are locked for short