Skip Unneeded Statistics Recollection | Teradata Vantage - Skipping Unneeded Statistics Recollection with the THRESHOLD Option - Analytics Database - Teradata Vantage

Database Administration

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-11-03
dita:mapPath
pgf1628096104492.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
ujp1472240543947
lifecycle
latest
Product Category
Teradata Vantage™
To skip unneeded statistics recollections, specify the THRESHOLD option of the COLLECT STATISTICS request. With this option, if the amount of data that changed since the last statistics collection is below a specified threshold or the statistics are newer than a specified age, Teradata does not recollect statistics. You have two options for creating a threshold:
  • Allow Vantage to determine the appropriate threshold
  • Specify a change percentage following the THRESHOLD keyword and/or the number of days

You can issue COLLECT STATISTICS requests at regular intervals without being concerned that you might be wasting I/O and CPU by collecting statistics too soon. Teradata skips the recollections for the statistics whose thresholds are not met and recollects the statistics for those columns that are over the threshold. For example, if the threshold is 10% data change and you request a recollection, Teradata skips recollection if less than 10% of the data changed since the last collection.

If you specify the THRESHOLD option the first time you collect statistics, Teradata collects the statistics and remembers the THRESHOLD option. When you recollect statistics, Teradata uses the saved threshold to determine whether statistics recollection is needed. To tell Teradata to ignore the THRESHOLD option (without resetting it) the next time it collects statistics, use the FOR CURRENT option after the percent specification.

To allow Vantage to determine the appropriate threshold, in the DBS Control utility disable both the DefaultTimeThreshold and DefaultUserChangeThreshold fields and set the SysChangeThresholdOption field to 0, 1, or 2. By default, DefaultTimeThreshold and DefaultUserChangeThreshold are disabled and SysChangeThresholdOption is 0. See Teradata Vantage™ - Database Utilities, B035-1102 for information on the different options for these fields.

You can query the LastCollectTimeStamp column of the Data Dictionary table DBC.StatsTbl to see whether data is collected or not.

The user-specified change threshold percent is not supported for statistics on views and queries.

For the syntax of the COLLECT STATISTICS statement, see Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.