The COLLECT STATISTICS (Optimizer Form) USING options apply to various sampling and threshold options that you can use to more finely tune the row sampling you specify or to avoid recollecting statistics when the data has not changed enough according to recollection thresholds you specify. You cannot specify USING options if you also specify the SUMMARY option for a COLLECT STATISTICS request. For more information about sampled and threshold statistics, see Reducing the Cost of Collecting Statistics by Sampling.
The following table describes the USING options.
Option | Description |
---|---|
MAXINTERVALS n | Maximum number of histogram
intervals to be used for the collected statistics. Vantage might adjust the specified maximum number of
intervals depending on the maximum histogram size. This option is valid for tables and constant expressions. The value for n must be an integer number. The valid range for n is 0 through 500. If you do not specify this option, Vantage determines the maximum number of intervals to use for the histogram. You can only specify this option if you also specify an explicit column or index. You cannot specify MAXINTERVALS n for a standard recollection of statistics on an implicitly specified index or column set. If you specify 0 intervals, the request only captures summary statistics such a the number of unique values, the number of nulls, and so on. The larger the number of intervals, the more optimal the granularity of the statistical data in the histogram. A finer granularity enables better single-table and join selectivity estimates for non-uniform data; however, you should apply this optimal granularity selectively because the larger the number of intervals, the larger the size of the histogram, which can increase query optimization time. |
MAXVALUELENGTH n | Maximum size for histogram values
such as MinValue, ModeValue, or MaxValue. The value for n must be an integer number.
This option is valid for both tables and constant expressions. If you specify a larger size than the maximum size, Vantage automatically adjusts the value to the maximum size. Vantage might also adjust the maximum value length you specify based on the size of the histogram that contains the statistics. If you do not specify a MAXVALUELENGTH, Vantage determines the maximum size of the intervals to be used for the histogram. For single-character statistics on CHARACTER and VARCHAR columns, n specifies the number of characters. For all other options, n specifies number of bytes. For multicolumn statistics, Vantage concatenates the values and truncates them if necessary to fit into the specified maximum size. The system does not truncate numeric values for single-column statistics. The system increases the interval size automatically if the specification is not sufficient to accommodate the full value for single-column statistics on numeric columns. For multicolumn statistics, if the maximum interval size truncates numeric statistical data, Vantage automatically increases the maximum interval size to accommodate the numeric column on the maximum size boundary. A larger maximum value size causes Vantage to retain the value until the specified maximum is reached, which can enable better single-table and join selectivity estimates for skewed columns. However, you should be selective when increasing the size for the required columns because increasing the maximum value size also increases the size of the histogram, which can increase query optimization time. You can only specify this option if you also specify an explicit column or index. You cannot specify MAXVALUELENGTH n for a standard recollection of statistics on an implicitly specified index or column set. |
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. You cannot specify NO SAMPLE for a
standard recollection of statistics on an implicitly specified
index or column set.
|
NO THRESHOLD |
You can only specify this option if you also specify an explicit column or index. You
cannot specify NO THRESHOLD for a standard recollection of
statistics on an implicitly specified index or column set.
|
NO THRESHOLD DAYS |
You can only specify this option if you also specify an explicit index or column set. You cannot specify NO THRESHOLD DAYS for a standard
recollection of statistics on an implicitly specified index or
column set.
|
NO THRESHOLD PERCENT |
You can only specify this option if you also specify an explicit column or index. You cannot specify NO THRESHOLD PERCENT for a standard
recollection of statistics on an implicitly specified index or
column set.
|
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. You should use the SYSTEM SAMPLE option instead of SAMPLE. |
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. The value for n can be a decimal number or integer from 2 through 100. Specifying SAMPLE 100 PERCENT is equivalent to collecting full statistics. You can only specify this option if you also specify an explicit index or column set. You cannot specify SAMPLE n PERCENT for a standard
recollection of statistics on an implicitly specified index or
column set.
|
SYSTEM MAXINTERVALS | Use the system-determined maximum
number of intervals for this histogram. This option is valid for tables. You can only specify this option if you also specify an explicit index or column set. You cannot specify SYSTEM MAXINTERVALS for a standard recollection of statistics on an implicitly specified index or column set. Use the system-determined maximum number of intervals for the histogram. |
SYSTEM MAXVALUELENGTH | Use the system-determined maximum
column width for histogram values such as MinValue, ModeValue, or
MaxValue. This option is valid for tables. You can only specify this option if you also specify an explicit index or column set. You cannot specify SYSTEM MAXVALUELENGTH for a standard recollection of statistics on an implicitly specified index or column set. |
SYSTEM SAMPLE | Scan a system-determined percentage
of table rows to collect statistics. Vantage may collect a sample of 100 percent several
times before downgrading the sampling percentage to a lower value. SYSTEM SAMPLE is the default option if you do not specify the SAMPLE option. You can only specify this option if you also specify an explicit index or column set. You cannot specify SYSTEM SAMPLE for a standard recollection of statistics on an implicitly specified index or column set. The DBS Control field SysSampleOption in the STATISTICS field group contains the default for this option. For more information, see Teradata Vantage™ - Database Utilities, B035-1102. |
SYSTEM THRESHOLD | Collect statistics only if the percentage of changed data or the age of the current statistics exceeds the currently specified threshold for the statistic. Vantage determines the appropriate change threshold to skip recollections if the changes are under this threshold. When doing this, the system takes into consideration the changes of update, delete and insert counts from the last collection of statistics, the history of column demographics, column usage, and Optimizer extrapolation techniques to determine the appropriate change threshold. If statistics are being collected first time, they are not skipped because there is no existing histogram data that can be used to determine the delta data change or age of the statistics. The change threshold can be different for different columns. You can only specify this option if you also specify an explicit index or column set. You cannot specify SYSTEM THRESHOLD for a standard recollection of statistics on an implicitly specified index or column set. |
SYSTEM THRESHOLD DAYS | Uses a change-based percentage as a
threshold for recollecting statistics. You can only specify this option if you also specify an explicit index or column set. You cannot specify SYSTEM THRESHOLD DAYS for a standard recollection of statistics on an implicitly specified index or column set. |
SYSTEM THRESHOLD PERCENT | Only applies to change percentage. This is the default if you do not specify a PERCENT threshold option. Vantage determines the appropriate change threshold to skip recollections if the percentage is under this threshold. When doing this, the system takes into consideration the percentage of change since the last update, delete, and insert counts from the last collection of statistics, the history of column demographics, column usage, and Optimizer extrapolation techniques to determine the appropriate change percentage threshold. You can only specify this option if you also specify an explicit index or column set. You cannot specify SYSTEM THRESHOLD PERCENT for a standard recollection of statistics on an implicitly specified index or column set. If you are collecting these statistics for the first time, they are not skipped because there is no existing histogram data that can be used to determine the percentage of change for the data. The DBS Control field SysChangeThresholdOption in the STATISTICS field group contains the default for this option. For more information, see Teradata Vantage™ - Database Utilities, B035-1102. |
THRESHOLD n DAYS | Do not to recollect statistics if
the age of the statistic is less than the number of days
specified. The value for n must be an integer number that represents the number of days. The valid range for n is 1 - 9999. You can only specify this option if you also specify an explicit index or column set. You cannot specify THRESHOLD n DAYS for a standard
recollection of statistics on an implicitly specified index or
column set.
The DBS Control field DefaultTimeThreshold in the STATISTICS field group contains the default for this option. For more information, see Teradata Vantage™ - Database Utilities, B035-1102. |
THRESHOLD n PERCENT | Do not to recollect statistics if
the percentage of data change since the last collection is less than
the specified percentage. The value for n can be either a decimal number or an integer number. The valid range of n is 1 - 9999.99. You can only specify this option if you also specify an explicit column or index. You
cannot specify THRESHOLD n
PERCENT for a standard recollection of statistics on an
implicitly specified index or column set.
The DBS Control field DefaultUserChangeThreshold in the STATISTICS field group contains the default for this option. For more information, see Teradata Vantage™ - Database Utilities, B035-1102. |