Neoview SQL Reference Manual (R2.4)

defined by the number of rows specified for period. The value period must be
an integer that is greater than zero (period > 0).
SET ROWCOUNT c
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 is not specified, Neoview SQL will determine the actual table
row count.
See “SAMPLE Clause” (page 298).
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 Neoview SQL chooses 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.
For information about automating UPDATE STATISTICS operations to run during a maintenance
window and to operate on a specific set of tables, see the Neoview Database Administrator's Guide.
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 because UPDATE STATISTICS
runs under that transaction. The transaction auto abort time could be exceeded during the
processing.
Neoview SQL runs multiple transactions, breaking down the long transaction.
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
UPDATE STATISTICS Statement 205