SAMPLE Definition (sample_definition)

A SAMPLE definition (sample_definition) defines the sample size, in other words the number of rows in a table that are to be used when statistics are updated.

Syntax

<sample_definition> ::=
  SAMPLE <
unsigned_integer> ROWS
| SAMPLE <unsigned_integer> PERCENT

Explanation

The database system manages statistics for each base table. These statistics are used to determine the best strategy for executing an SQL statement. The statistics are stored in the database catalog by the UPDATE STATISTICS statement.

If a SAMPLE definition is specified in an UPDATE STATISTICS statement, it specifies the number of rows in the table that are to be used to calculate the statistics (sample size).

If a SAMPLE definition is not specified in an UPDATE STATISTICS statement and if it is not mandatory that all of the rows in the table be used to calculate the statistics, the database system uses the appropriate SAMPLE definition of the CREATE TABLE or ALTER TABLE statement.

The number of rows for which the UPDATE STATISTICS statement is to be executed can be defined by specifying a numeric (ROWS) or percentage value (PERCENT).

?     If a SAMPLE definition is specified as a PERCENT, the unsigned integer must be between 0 and 100.

?     If a SAMPLE definition is not defined, the database system uses the value 20000 ROWS.

Sample Size 0

You can exclude individual tables from an UPDATE STATISTICS run by setting a sample size of 0 for these tables with an ALTER TABLE statement.

SQL Statements in Which the SAMPLE Definition Can be Used

CREATE TABLE statement

ALTER TABLE statement

UPDATE STATISTICS statement