15.00 - Collecting and Recollecting Statistics Is Often Time Consuming - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

Teradata Database
Programming Reference

Collecting and Recollecting Statistics Is Often Time Consuming

Because Teradata Database always does a full-table scan (or an index scan if collecting statistics on an index) when it collects full statistics on a table (except when only PARTITION statistics are collected), the process can take a long time to complete. See “Reducing the Cost of Collecting Statistics by Sampling” on page 179. The exception to this is collecting statistics on the system-derived column PARTITION columns, which is always a fast operation. The duration of the scan is dependent on the size of the table, the system configuration, and the workload on the system.

Additional resources are consumed by the creation of interval histograms and the computation of the various statistical measures used to summarize the characteristics of a column set or index. For a definition of interval histograms, see SQL Request and Transaction Processing.

Columns that are not indexed or that are unique indexes take more time to process than NUSI columns.

Because of the time it can take to collect and recollect statistics, you should consider specifying one or more of the USING options, particularly those that establish recollection thresholds, to establish rules for when Teradata Database should recollect statistics. For more information about these options, see “Reducing the Cost of Collecting Statistics by Sampling” on page 179, “Comparison of Full and Sampled Statistics” on page 180, and “Using the THRESHOLD Options to Collect and Recollect Statistics” on page 180.

You should always collect statistics on newly created, empty tables. This defines the columns, indexes, and partitions for a partitioned table, as well as the synoptic data structures for subsequent collection of statistics and demographics. For a definition of synoptic data structure, see SQL Request and Transaction Processing.

You should also recollect statistics on newly created tables as soon as they are populated, and thereafter as needed.

When you perform a HELP STATISTICS request on an empty table, the Unique Value column returns a value of 0.