Neoview SQL Reference Manual (R2.5)

PERIODIC size ROWS EVERY period ROW
directs Neoview SQL to choose the first size number of rows from each period
of rows. The value size must be an integer that is greater than zero and less than
or equal to the value period. (0 < size <= period). The size of the period is
defined by the number of rows specified for period. The value period must be
an integer that is greater than zero (period > 0).
persistent-sample-option
r rows
A row sample is used to read r rows from the table. The value r must be an integer that is
greater than or equal to zero (r > 0).
RANDOM percent PERCENT
directs Neoview SQL to choose rows randomly from the table. The value percent must be a
value between zero and 100 (0 < percent < 100). In addition, only the first four digits to the
right of the decimal point are significant. For example, value 0.00001 is considered to be
0.0000, Value 1.23456 is considered to be 1.2345.
ALL
removes all persistent sample tables from the table. Only valid with the UPDATE STATISTICS
table REMOVE SAMPLE ALL command.
CREATE SAMPLE persistent-sample-option
creates a persistent sample table of size r rows or RANDOM percent percent. CREATE
SAMPLE ALL is not valid and results in a syntax error. The compiler uses persistent sample
tables to improve cardinality estimates for cases when estimates from histograms cannot be
considered reliable.
REMOVE SAMPLE persistent-sample-option
removes a persistent sample table as specified by r rows, RANDOM percent percent, or
ALL for all persistent sample tables in the table.
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.
While UPDATE STATISTICS is running on a table, the table is active and available for query
access.
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
time interval and to operate on a specific set of tables, see the Neoview Database Administrator's
Guide.
Sampling
As a guideline, the default sample of 1 percent of the rows in the table, with a maximum of 1
million rows provides good statistics for the optimizer to generate good plans.
UPDATE STATISTICS Statement 207