- Set thresholds for collecting statistics based on how much time has passed or percentage the data set has changed since the last collection. This prevents over-collecting statistics.
- Set the sampling method to generate a histogram based on a representative sample of a subset of table rows instead of all table rows. This saves collection time. Sampling is useful if you are collecting statistics for very large tables with a lot of non-unique data. You can also set the maximum interval and maximum value length of the histogram.
- Set the maximum number of histogram intervals and maximum size for histogram values.
When setting the Max interval, the larger the number, the more optimal the granularity of the statistical data in the histogram. A finer granularity enables better single-table and join selectivity estimates for non-uniform data; however, the larger the number of intervals, the larger the size of the histogram, which can increase query optimization time.
When setting the Max value length, a larger maximum value size causes Teradata Database to retain the value until the specified maximum is reached. This can enable better single-table and join selectivity estimates for skewed columns; however, increasing the maximum value size also increases the size of the histogram which can increase query optimization time. For important benefits and limitations, see Teradata® Database SQL Data Definition Language - Detailed Topics.
| Setting | Description |
|---|---|
| System-defined | Use if you want the Teradata Database to set the thresholds, sampling methods, and histogram interval and length. |
| User-defined | Use if you want to specify these settings. |
| None | Use if you do not want to set thresholds or a sampling method. |
There are two ways to change collect settings. You can either edit a single statistic or multiple statistics. Editing a single statistic gives you full control of all settings. Editing multiple statistics limits the settings you can change to System-defined and None; however, you can edit collect settings for all databases, objects on a single database, or a single object.