15.10 - USING Clause - Teradata Database

Teradata Database SQL Data Definition Language Syntax and Examples

Product

Teradata Database

Release Number

15.10

Published

December 2015

Language

English (United States)

Last Update

2018-06-05

dita:mapPath

SQL_DDL_15_10.ditamap

dita:ditavalPath

ft:empty

One or more using options. You must separate multiple options with the keyword AND. You cannot specify USING options with the SUMMARY option.

SAMPLE

Scan a system-determined percentage of table rows to collect the specified statistics. SAMPLE has the same meaning as SYSTEM SAMPLE and is only provided for backward compatibility to enable existing COLLECT STATISTICS scripts that specify the USING SAMPLE option to continue to run. Use the SYSTEM SAMPLE option instead of SAMPLE.

Geospatial NUSIs are always sampled at 100%.

SYSTEM SAMPLE

Scan a system-determined percentage of table rows to collect statistics.

Teradata Database may collect a sample of 100% several times before downgrading the sample percentage to a lower value.

This is the default if you do not specify a sample option, unless it is overridden by the setting of the DBSControl parameter SysSampleOption or the cost profile constant StatsSysSampleOption.

You can only specify this option if you also specify an explicit column or index. You cannot specify SYSTEM SAMPLE for a standard recollection of statistics on an implicitly specified column or index set.

Geospatial NUSIs are always sampled at 100%.

SAMPLE n PERCENT

Scans the percentage of table rows that you specify rather than scanning all of the rows in the table to collect statistics, where n is a decimal or an integer from 2 through 100.

Specifying SAMPLE 100 PERCENT is equivalent to collecting full statistics.

For the first collection of statistics, the specified sample percentage overrides the default specified in the setting of the DBSControl parameter SysSampleOption or the cost profile constant StatsSysSampleOption.

For recollection of statistics, SAMPLE n PERCENT overrides any previous SAMPLE option specifications and instead scans n percent of the rows in the table.

Geospatial NUSIs are always sampled at 100%.

NO SAMPLE

Use a full-table scan to collect the specified statistics.

You can only specify this option if you also specify an explicit index or column set.

Geospatial NUSIs are always sampled at 100%.

SYSTEM THRESHOLD

Do not collect statistics if the percentage of changed data and the age of the current statistics are below the values determined by the system.

This option is valid only for tables.

You can only specify this option if you also specify an explicit index or column set.

You cannot use this option to collect statistics on geospatial NUSIs.

SYSTEM THRESHOLD PERCENT

Change the percentages. This is the default if you do not specify a PERCENT threshold option unless it is overridden by the setting of the DBSControl parameters DefaultUserChangeThreshold and SysChangeThresholdOption or the cost profile constants StatsDefaultUserChangeThreshold and StatsSysChangeThresholdOption.

This option is valid only for tables.

You can only specify this option if you also specify an explicit index or column set.

You cannot use this option to collect statistics on geospatial NUSIs.

SYSTEM THRESHOLD DAYS

The age in days of the current statistics. This is the default if you do not specify a DAYS threshold option unless it is overridden by the setting of the DBSControl parameter DefaultTimeThreshold or the cost profile constant StatsDefaultTimeThreshold.

This option is only valid for tables.

The system does not enforce day thresholds, so recollection of statistics is determined by the PERCENT threshold option.

You can only specify this option if you also specify an explicit index or column set.

You cannot use this option to collect statistics on geospatial NUSIs.

THRESHOLD n PERCENT

Recollect statistics if the percentage of change in the statistics exceeds the specified percentage, where n is a decimal or an integer.

Statistics are not recollected if:

the age of the statistics and the percentage of change do not exceed the values specified for THRESHOLD n DAYS and THRESHOLD n PERCENT.
the percentage of change does not exceed the number specified in THRESHOLD n PERCENT and NO THRESHOLD DAYS is specified.

You cannot use this option to collect statistics on geospatial NUSIs.

THRESHOLD n DAYS

Recollect statistics if the age of the statistic is greater than or equal to the number of days specified, where n is an integer from 1 through 9999.

This option is only valid for tables.

You can only specify this option if you also specify an explicit index or column set.

Statistics are not recollected if:

the age of the statistics and the percentage of change do not exceed the values specified for THRESHOLD n DAYS and THRESHOLD n PERCENT.
the age of statistics does not exceed the specified number of days specified in THRESHOLD n DAYS and NO THRESHOLD PERCENT is specified.

You cannot use this option to collect statistics on geospatial NUSIs.

NO THRESHOLD

One of the following:

Do not apply any thresholds to the collection of statistics.
Remove the existing threshold before collecting the statistics.

You can only specify this option if you also specify an explicit index or column set.

You cannot use this option to collect statistics on geospatial NUSIs.

NO THRESHOLD PERCENT

One of the following:

Do not apply a PERCENT change threshold to the collection of statistics.
Remove the existing PERCENT change threshold before collecting the statistics.

You can only specify this option if you also specify an explicit index or column set.

The effect of this option varies, depending on whether the request is for a first collection of statistics or for a recollection:

For the first collection of statistics, NO THRESHOLD PERCENT overrides the default setting of the DBSControl parameters SysChangeThresholdOption and DefaultUserChangeThreshold or the cost profile constants StatsSysChangeThresholdOption and StatsDefaultUserChangeThreshold.
For recollections of statistics, NO THRESHOLD PERCENT overrides any previous change threshold percent specification.

Statistics are not recollected if the age of the statistics does not exceed the specified number of days specified in THRESHOLD n DAYS and NO THRESHOLD PERCENT is specified.

You cannot use this option to collect statistics on geospatial NUSIs.

NO THRESHOLD DAYS

One of the following:

Do not apply a DAYS threshold to the collection of statistics.
Remove the existing DAYS threshold before collecting the statistics.

You can only specify this option if you also specify an explicit index or column set.

For the first collection of statistics, NO THRESHOLD DAYS overrides the default setting of the DBSControl parameter DefaultTimeThreshold or the cost profile constant StatsDefaultTimeThreshold.

For recollection of statistics, NO THRESHOLD DAYS overrides any previous THRESHOLD n DAYS specification.

Statistics are not recollected if the percentage of change does not exceed the number specified in THRESHOLD n PERCENT and NO THRESHOLD DAYS is specified.

You cannot use this option to collect statistics on geospatial NUSIs.

SYSTEM MAXINTERVALS

The system-determined maximum number of intervals is used for this histogram.

You can only specify this option if you also specify an explicit index or column set.

This option is only valid for tables.

You cannot use this option to collect statistics on a geospatial NUSI.

MAXINTERVALS n

Specifies the maximum number of histogram intervals to be used for the collected statistics, where n an integer from 0 through 500. Teradata Database may adjust the specified maximum number of intervals depending on the maximum histogram size.

This option is valid only for tables.

You can only specify this option if you also specify an explicit index or column set.

If the optimizer uses summary statistics most of the time and rarely uses detailed statistics, consider lowering the MAXINTERVALS value you specify to 0 to make the Optimizer aware of how it is using statistics. However, make sure that the performance of queries that use detailed statistics is not degraded because of this change.

You cannot use this option to collect statistics on a geospatial NUSI.

SYSTEM MAXVALUELENGTH

The system-determined maximum column width for histogram values such as MinValue, ModeValue, MaxValue, and so on.

This option is valid only for tables.

You can only specify this option if you also specify an explicit index or column set.

You cannot use this option to collect statistics on a geospatial NUSI.

MAXVALUELENGTH n

Specifies the maximum size for histogram values such as MinValue, ModeValue, MaxValue, and so on, where n is an integer.

For single-character statistics on CHARACTER and VARCHAR columns, n specifies the number of characters. For all other options, n specifies number of bytes.

You can only specify this option if you also specify an explicit index or column set.

You cannot use this option to collect statistics on a geospatial NUSI.

This option is valid only for tables.

FOR CURRENT

The USING options specified for the current statistics recollection request are to be used only for the current COLLECT STATISTICS request and that the previously specified options for the request are to be used for any future recollections of statistics, unless otherwise overridden.