Collecting statistics provides the Optimizer with the data demographics needed to generate good query plans. The more accurate and up-to-date the statistics, the better the Optimizer can decide on plans and choose the fastest way to answer a query. The computed results are stored in the Data Dictionary DBC.StatsTbl for use during the optimizing phase of statement parsing. The Optimizer does not accept pragmas or hints, and bases query plans only on statistics.
Required Privileges
You must have STATISTICS privileges on a table (except a volatile table) to run a COLLECT STATISTICS request.
Resource Considerations
Collecting statistics typically involves a full-table scan, so you may need to collect statistics for large tables during off-hours.
For large tables, you can also collect statistics on a sampled subset of table rows instead of all the rows for a table, using the SAMPLE clause. For more information, see Sampling Statistics with the USING SAMPLE Option.