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.

Structure

Syntax Syntax

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

End of the code.

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 STATISTCS statement is to be executed can be defined by specifying a number of rows (ROWS) or percentage value (PERCENT).

If a SAMPLE definition is specified as a PERCENT value, the specified unsigned integer must be between 0 and 100.
If a SAMPLE definition is not defined, the database system uses the value 20,000 ROWS.

Sample Size 0

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

Sample Type and Size for Updating the SQL Optimizer Statistics
Number of Data Records (Sample Type Rows)	Proportion of Data Records as Percentage of all Data Records in the Table (Sample Size Percent)	Description
0	0	The system does not update any statistics for the table.
0 < Number of data records < Number of data records in the table The default value for the sample size is 20,000 data records.	0 < Percentage of data records < 100 % For large tables (> 1,000,000 data records) in SAP systems, we recommend a sample size of 5 %.	The system uses the sample size specified to estimate the statistics.
Number of all data records in the table	100 %	The system calculates the statistics. This usually takes much longer than an estimation using a sample.

More Information

CREATE TABLE Statement, ALTER TABLE Statement, UPDATE STATISTICS Statement

Concepts of the Database System, SQL Optimizer, SQL Optimizer Statistics, Displaying the Search Strategies Used by SQL Statements (EXPLAIN)

Database Administration in CCMS, SQL Optimizer in CCMS, Scheduling Updates of the SQL Optimizer Statistics

SAP Notes 808060, 927882