Neoview SQL Reference Manual (R2.5)

Authorization and Locking
To run the UPDATE STATISTICS statement against SQL tables, you must have the authority to
read the user table for which statistics are generated. Because the histogram tables are registered
in the schema of table, you must have the authority to read and write to this schema.
UPDATE STATISTICS momentarily locks the definition of the user table during the operation
but not the user table itself. The UPDATE STATISTICS statement uses READ UNCOMMITTED
isolation level for the user table.
Transactions
Do not start a transaction before executing UPDATE STATISTICS. UPDATE STATISTICS runs
multiple transactions of its own, as needed. Starting your own transaction in which UPDATE
STATISTICS runs could cause the transaction auto abort time to be exceeded during the
processing.
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:
Multicolumn GroupSingle-Column Group Within
Parentheses
Single-Column Group
ON (CITY, STATE) or ON
(CITY,STATE,ZIP)
ON (CITY),(STATE),(ZIP)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
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
multi-column histogram.
You can clear statistics in any combination of columns you specify, not necessarily with the
column-group-list you used to create statistics. However, those statistics will remain until
you clear them.
Column Lists and Access Plans
Generate statistics for columns most often used in data access plans for a table—that is, the
primary key, indexes defined on the table, and any other columns frequently referenced in
predicates in WHERE or GROUP BY clauses of queries issued on the table. Use the EVERY
COLUMN option to generate histograms for every individual column or multicolumns that
make up the primary key and indexes.
The EVERY KEY option generates histograms that make up the primary key and indexes.
If you often perform a GROUP BY over specific columns in a table, use multi-column lists in the
UPDATE STATISTICS statement (consisting of the columns in the GROUP BY clause) to generate
histogram statistics that enable the optimizer to choose a better plan. Similarly, when a query
joins two tables by two or more columns, multi-column lists (consisting of the columns being
joined) help the optimizer choose a better plan.
208 SQL Statements