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

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—544517-008
2-267
Considerations for UPDATE STATISTICS
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-75.
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
periods, and timeout errors do not occur. If the lock is held for a longer time, multiple
retry attempts help to complete concurrent operations with minimum timeout
interruption.
Generating and Clearing Statistics for Columns
To generate statistics for particular columns, name each column, or name the first and
last columns of a sequence of columns in the table. For example, suppose that a table
has consecutive columns CITY, STATE, ZIP. This list gives a few examples of possible
options you can specify:
The TO specification is useful when a table has many columns, and you want
histograms on a subset of columns. Do not confuse (CITY) TO (ZIP) with (CITY,
STATE, ZIP), which refers to a multicolumn histogram.
Single-Column Group Single-Column Group
Within Parentheses
Multicolumn Group
ON CITY, STATE, ZIP ON (CITY),(STATE),(ZIP) ON (CITY, STATE) or
ON (CITY,STATE,ZIP)
ON CITY TO ZIP ON (CITY) TO (ZIP)
ON ZIP TO CITY ON (ZIP) TO (CITY)
ON CITY, STATE TO ZIP ON (CITY), (STATE) TO (ZIP)
ON CITY TO STATE, ZIP ON (CITY) TO (STATE), (ZIP)