15.00 - Using the THRESHOLD Options to Collect and Recollect Statistics - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1184-015K
Language
English (United States)

Using the THRESHOLD Options to Collect and Recollect Statistics

Statistics collection thresholds enable you to minimize the unnecessary collection of statistics. For a system‑determined threshold, the Optimizer automatically determines whether a recollection is needed or whether extrapolation is adequate. You can collect and recollect threshold statistics on tables.

The THRESHOLD options enable you to automatically skip recollecting statistics if the amount of data change since the last statistics collection or the age of the current statistics is below the thresholds in effect for the statistics.

The Optimizer can automatically determine the appropriate thresholds to apply based on previously collected statistics, column histories, change counts, and other factors, or you can explicitly specify the thresholds as part of a COLLECT STATISTICS request by specifying a change percentage, a number of days, or both.

You can submit collect statistics requests at regular intervals, and the Optimizer can use stored threshold and historical data to determine whether the specified recollection of statistics is necessary or not. The Optimizer can use the same data to determine when to recollect statistics for those columns that exceed the specified threshold. For example, if you determine that the threshold is to be a 10% data change and you submit a request to recollect statistics, the Optimizer does not recollect those statistics if the change in the data from the last collection is less than 10%.

If you specify a THRESHOLD option for first time statistics collections, the Optimizer collects the statistics and stores the THRESHOLD options you specified for future use without consulting the threshold values you specify. The Optimizer uses the saved collection thresholds to determine whether the current statistics collection can be skipped or not when you submit recollection requests at a later time.

Specifying a new THRESHOLD option for statistics recollection overrides the current setting, if any. The Optimizer applies the new THRESHOLD value to the current request, and remembers the updated THRESHOLD option for future statistics recollection.

You can specify the following kinds of thresholds to control recollecting statistics.

  • Change‑based
  • Time‑based
  • For a change‑based threshold, the optimizer consults the system maintained UDI (Update, Delete, Insert) counts, random AMP samples and available table history to determine the amount of data change from the last statistics collection.

    You can also specify a combination of change-based and time-based thresholds in a single COLLECT STATISTICS request.

    For SYSTEM THRESHOLD or SYSTEM THRESHOLD PERCENT, the Optimizer considers column history and internal extrapolations techniques to determine the appropriate change threshold. When the Optimizer recollects full statistics for a small table NUSI, for example, it ignores threshold information and collects full statistics because, in that case, collecting full statistics provides more accurate statistics for a very small cost.

    A time‑based threshold uses the age of the current statistics to determine when statistics are refreshed.

    Note: For SYSTEM THRESHOLD DAYS, the system uses a change-based threshold for recollecting statistics.

    You can query the dictionary table column DBC.StatsTbl.LastCollectTimeStamp to determine whether the statistics are collected or skipped. See Data Dictionary for details about DBC.StatsTbl and its associated system views.

    The following table describes various threshold scenarios, including the corresponding signatures saved in DBC.StatsTbl.

     

            THRESHOLD Option

        ThresholdSignature

    Column in DBC.StatsTbl

                           Explanation

    None

     

    One of two possibilities.

  • Null
  • A signature based on the cost profile constants.
  • Teradata Database takes the default threshold settings from several cost profile constants.

    SYSTEM THRESHOLD

     

    SCTnnnn.nnSTTnnnn

    System-determined change threshold.

    If the percentage of changes to the table from the last collection time is not more than system-determined change threshold, skip the recollection.

    THRESHOLD 10 PERCENT

     

    UCT0010.00STTnnnn

    User-specified change threshold.

    If the table data has not changed more than 10 percent from the last statistics collection time, skip the recollection.

    THRESHOLD 15 DAYS

     

    SCTnnnn.nnUTT0015

    User-specified time threshold and system-determined change threshold.

    If the age of the current statistics is not more than 15 days and the table is not changed more than the system‑determined percentage change threshold, skip the recollection.

    THRESHOLD 10 PERCENT AND THRESHOLD 15 DAYS

     

    UCT0010.00UTT0015

    User-specified change and time thresholds.

    If the table has not changed more than 10 percent and the age of the current statistics is not more than 15 days, skip the recollection.

    NO THRESHOLD PERCENT AND THRESHOLD 15 DAYS

     

    UCTnoneþþþUTT0015

    User-specified change and time threshold. If the age of the current statistics is not more than 15 days, skip recollection. The NO THRESHOLD PERCENT clause indicates not to check for change threshold.

    THRESHOLD 9999 PERCENT AND THRESHOLD 9999 DAYS

     

    UCT9999UTT9999

    The setting has an effect of skipping the recollections for a long time. If the change is more than 9999% (which is possible for small tables) the statistics are allowed to be recollected.

    NO THRESHOLD

     

    UCTnoneþþþUTTnone

    Disable all thresholds for the column set or index specified in the collect statistics statement. This is equivalent to specifying NO THRESHOLD PERCENT AND NO THRESHOLD DAYS.

                                                                                    Key

    SCT =

    UCT =

    STT =

    UTT =

    nnnn.nn =

    þ =

    System‑determined Change Threshold

    User‑specified Change Threshold

    System‑determined Time Threshold

    User‑specified Time Threshold

    Change threshold value

    Blank

  • Encoding for a system‑determined percent change threshold is xxxx.xx.
  • Encoding for a user‑specified change threshold is nnnn.nn, where nnnn.nn is a user‑specified percent change value.
  • Encoding for no threshold is noneþþþ
  • Encoding for a system‑determined time threshold is xxxx.
  • Encoding for a user‑specified time threshold is nnnn, where nnnn is a user‑specified number of days.
  • Encoding for no threshold is none.