COLLECT STATISTICS (SQL)
Synopsis
COLLECT STATISTICS FOR TABLE tablename [ sample ]
COLLECT STATISTICS FOR SCHEMA schemaname [ sample ]
Description
The COLLECT STATISTICS command runs the utility that gathers collected statics and stores them as part of the table’s extent index. These collected statistics
Arguments
tablename
The name of an existing table from which to gather statistics. The table name can be qualified (schema.table), or unqualified (table). An unqualified table name takes the default schema name.
tablename
The name of an existing schema from which to gather statistics.
sample
You may choose to specify an option clause indicating what percentage of rows of the table should be used to sample the data for the collection utility. The syntax for this option is as follows:
%SAMPLE_PERCENT percentage
The percentage can be specified as '.##' or '##%'; for example, either '.12' or '12%' will cause the command to use 12% of the rows in the table when sampling the data. Specify percentage with a value greater than 0 and less than or equal to 100%; a value out of this range issues an SQLCODE -1 error.
Usually, you do not need to specify a sample percentage. Nonetheless, it is particularly useful when potential outlier values for a field are not evenly distributed among rows throughout the table.
For any table with an extent size of greater than 1000 rows, the entire extent will be used by TUNE TABLE regardless of the %SAMPLE_PERCENT value.
Examples
The following example gathers collected statistics from the Sample schema:
COLLECT STATISTICS Sample.MyTest
The following example gathers collected statistics by sampling 30% of the Sample.MyTest table:
COLLECT STATISTICS Sample.MyTest %SAMPLE_PERCENT '30%'
