Sample Statistics | Teradata Vantage - Sampling Statistics with the USING SAMPLE Option - Advanced SQL Engine - Teradata Database

If you find that recollection on columns with evenly distributed data (that is, data with little or no data skew) is too costly in terms of performance, try using sampled statistics instead.

To use sample statistics, specify the USING SAMPLE option of the COLLECT STATISTICS request. You can collect sample statistics with a system-determined sample or you can specify a sample percentage. If you specify the sample percentage, Teradata reads the specified percent of rows to collect statistics.

If Teradata determines the sample percent, the system determines when to switch to sampling and the appropriate degree of sampling that can be used without sacrificing quality. The downgrade approach works like this:

1. The first time you request a sample statistics collection, Teradata collects full statistics. This gives Teradata information it needs to determine when to downgrade to a smaller percentage.
2. On subsequent requests to recollect statistics, Teradata collects full statistics until it has collected enough statistics history.
3. Using the statistics history, Teradata evaluates the nature of the column (for example, skewed, rolling, or static). Teradata considers the column usage (detailed or summary data), which it maintains in DBC.StatsTbl.UsageType field, and the user-specified number of intervals to decide when to downgrade to sample statistics. The system is more aggressive in downgrading to sample statistics for the histograms whose summary data is used.
4. Teradata determines a sample percentage (2% to 100%) for recollecting statistics and decides which formula to use for scaling based on the history and nature of the column.
5. To ensure quality, Teradata compares the recollected statistics to the history. The system lowers the sampling percentage only if a smaller sample provides quality results.

For example, Teradata never switches to sampled statistics for small tables, skewed columns, a column that is member of the partitioning expression, or columns that require details.

After few sample statistics collections, Teradata may collect full statistics to verify and adjust the sample percentage and the extrapolation method.

Sample statistics are not supported on views and queries.

To determine the sampling percentage in use, you can query the Data Dictionary table column DBC.StatsTbl.SampleSizePct.

For more information, also see “Sampled Statistics” in Teradata Vantage™ - SQL Request and Transaction Processing, B035-1142.