Neoview SQL Reference Manual (R2.4)

NECESSARY COLUMN[S]
The NECESSARY COLUMN[S] keyword causes UPDATE STATISTICS to determine
what existing histograms, if any, need to be updated, then updates them. If no histograms
are found to need regeneration, UPDATE STATISTICS issues a warning.
The NECESSARY COLUMN[S] keyword ignores the SAMPLE clause and internally
determines the sample percent to use.
histogram-option
GENERATE n INTERVALS
The GENERATE n INTERVALS option for UPDATE STATISTICS now accepts values
between 1 and 10,000. Keep in mind that increasing the number of intervals per
histograms may have a negative impact on compile time.
Increasing the number of intervals can be used for columns with small set of possible
values and large variance of the frequency of these values. For example, 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.
SAMPLE [sample-option] [SET ROWCOUNT c]
is an optional clause that specifies that sampling is to be used to gather a subset of
the data from the table. UPDATE STATISTICS uses a temporary table to store the
sample results and generates histograms.
If you specify the SAMPLE clause without additional options, a random row sample
is used to read 1 percent of the rows in the table, with a maximum of 1 million rows.
If you specify the ROWCOUNT option, Neoview SQL reads 1 percent of c, with a
maximum of 1 million rows.
If you do not specify the SAMPLE clause, table has fewer rows than specified, or the
sample size is greater than the system limit. Neoview SQL reads all rows from table.
See “SAMPLE Clause” (page 298).
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).
If you specify the ROWCOUNT clause, r must be less than or equal to c (r < r). The
percentage is determined by the equation r/c * 100.
RANDOM percent PERCENT [CLUSTERS OF blocks BLOCKS}
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.
CLUSTERS OF blocks BLOCKS
specifies the number of blocks that compose the cluster. The value block must
be an integer that is greater than or equal to zero (blocks > 0).
PERIODIC size ROWS EVERY period ROWS
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
204 SQL Statements