17.05 - Boundary Options - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)

The following table lists the valid boundary option values and their definitions.

Option Definition
CHANGERATE Every column appearing in an UPDATE SET clause is automatically assigned a change rating ranging from 0 to 9, representing the frequency at which it is updated. For more information about change ratings, see .

The system assigns columns with a very high update frequency a change rating of 9, while columns that are never updated are assigned a change rating of 0.

Columns with a change rating greater then this user-specified ChangeRate parameter are not considered during index analysis for potential indexes. In other words, the ChangeRate value is a threshold for determining whether to use columns for an index analysis or not.

If the specified ChangeRate parameter is 9, the change rating system is not used to disqualify columns from index analysis.

The default is 5.

The valid range is 0 to 9, where 0 means the column is not updated and 9 means the column is highly volatile with respect to the specified workload.

COLUMNSPERINDEX Maximum number of composite secondary index columns to be considered for index recommendation by the Index Wizard.

The default is 4.

The valid range is 1 to 64.

COLUMNSPERJOININDEX Maximum number of referenced columns in a single-table join index to be considered for index analysis by the Index Wizard.

The default is 8.

The valid range is 1 to 16.

INDEXMAINTMODE Controls how estimated index maintenance costs are used during analysis. The following values are supported.
  • 0

    Maintenance costs are not estimated.

    The feature is disabled.

  • 1

    Maintenance costs are estimated only as supplemental information for the final index recommendations.

    This is the default.

  • 2

    Maintenance costs are used to evaluate and choose between candidate indexes and are also included with the final recommendations.

INDEXESPERTABLE Maximum number of new indexes, including secondary and join indexes, that can be recommended per table.

The default is 16.

The system limit is 32. Each value-ordered secondary index reduces the number of consecutive indexes by 2.

The valid range is 1 to 32.

SEARCHSPACE Maximum size of the search space to be allowed for evaluating index candidates. The higher the number you specify, the more candidate indexes the system can evaluate.

The number specified for SEARCHSPACE corresponds to an internal variable that is used to control the size of the search space evaluated by the index search algorithm.

Keep in mind that the larger the number you specify, the longer the evaluation time. To shorten your evaluation times, change the specification for SearchSpace to a lower number.

The default is 256.

The valid range is 128 to 512.