Neoview SQL Reference Manual (R2.2)

consider a column ‘CITY’ in table SALES, which stores the city code where the item
was sold, where number of cities in the sales data is 1538. Setting the number of
intervals to a number greater or equal to the number of cities (that is, setting the
number of intervals to 1600) guarantees that the generated histogram captures the
number of rows for each city. If the specified value n exceeds the number of unique
values in the column, the system generates only as many intervals as there are unique
values.
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, Neoview SQL determines the number of rows
in the table either by estimation or SELECT COUNT(*).
See “SAMPLE Clause” (page 260).
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.
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 (for SQL tables) of table, you must have the authority to read and write to this
schema. Then, when the two histogram tables are created, you become the owner of the tables.
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
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.
If you do not start a transaction for UPDATE STATISTICS, Neoview SQL runs multiple
transactions, breaking down the long transaction.
UPDATE STATISTICS Statement 177