using_option - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Published
January 2021
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
ncd1596241368722.ditamap
dita:ditavalPath
hoy1596145193032.ditaval
dita:id
B035-1144
lifecycle
previous
Product Category
Teradata Vantage™


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.
The 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 field SysSampleOption.
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.
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.
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.
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.
  • 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.
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. The 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.