Advanced Index Parameters

You can set various parameters that determine index recommendations.

To set advanced index parameters

1 From the Index Analysis dialog box, click Advanced.

The Index Analysis Parameters dialog box appears.

Note: You can also open this dialog box by clicking Tools > Options > Index Analysis Inputs. For more information, see “Index Analysis Inputs” on page 142.

2 Fill in the dialog box. See “To start an Index analysis” on page 81.

Note: The following fields and buttons are enabled with Teradata Database V2R6.1 or later: Maximum Columns Per Join Index, Index Maintenance Mode, and Index Types.

Option	Description
Input Options
Keep Existing Indexes	Retains the existing indexes on the tables selected for analysis. To drop the indexes that may not be necessary, select No. The default is No.
Maximum Candidate Indexes	The maximum number of candidate indexes per table to be searched when recommending an index. The default is 256. The valid range is 128 to 512.
Maximum Indexes Per Table	The maximum number of new indexes, including secondary and join indexes, that can be recommended per table. This option can be used to restrict the number of index recommendations on a given table. The default is 16. The valid range is 1 to 32.
Maximum Columns Per Secondary Index	The maximum number of composite secondary index columns to be considered in the index as specified by the INITIATE INDEX ANALYSIS statement used to start this analysis. The default is 4. The valid range is 1 to 64.
Maximum Columns Per Join Index	The maximum number of referenced columns in a single-table join index to be considered for index analysis. The default is 6. The valid range is 1 to 16. Note: Although the Teradata Database supports single column join indexes with as many columns that can fit within the system row size limit, Teradata Index Wizard is limited to recommendations involving no more than 16.
Change Rate Threshold	Specifies if columns are used for an index analysis. Columns with a change rating greater than specified are not considered during index analysis for potential indexes. Every column appearing in an UPDATE SET clause is automatically assigned a change rating ranging from 0 to 9, representing how frequently it is updated. The columns that are frequently updated, are assigned a very high update frequency of 9, while columns that are never updated are assigned a change rating of 0. For example, if the value is specified as 5, all columns having a change rating of 5 or less are considered for an index. This value is common for all columns to be considered for index analysis. The default threshold is 5. The valid range is 0 to 9.
Index Maintenance Mode	Controls how estimated index maintenance costs are used during analysis. Choose from the following: Off: Maintenance costs are not estimated (feature is turned off). Report Only: Maintenance costs are estimated only as supplemental information for the final index recommendations. Evaluate: Maintenance costs are used to evaluate and choose between candidate indexes and are also included along with the final recommendations. The default is Report Only.
Use Modified Statistics	Specifies that modified statistics should be considered during the index analysis, if they are available. Modified statistics are read from the TableStatistics table of the QCD from the ModifyStats column rather than the statistics gathered with COLLECT STATISTICS (QCD Form). If the ModifiedStats column is not populated, then the statistics stored in StatisticsInfo are used instead and the result is the same as if you had not specified this option. If the modified statistics of a given column are older than the statistics recorded for the same column as part of another plan capture, then the latest among them is used.
Checkpoint Frequency	Specify the number of queries after which a checkpoint snapshot is inserted. It must be a positive integer value. To have the CHECKPOINT clause ignored, type 0. To have no checkpoint information saved during an index analysis, leave this field blank. To have no checkpoint snapshot taken, the value in this field must be greater than the total number of queries to be analyzed. If the index analysis is halted for any reason, such as a Teradata Database restart or an Teradata Index Wizard restart, perform a new analysis. Specifying the checkpoint frequency allows a restart at a later time on the halted analysis.
Time Limit	Specify a time limit, in minutes, in which the analysis is to be performed. The maximum value is 2880 minutes. By default there is no time limit. Time Limit is the only input option applicable to a Partition Analysis. Note: The Time Limit option is available for Teradata 12.0 database versions. For Teradata Database versions earlier than 12.0, the option is disabled.
Index Types	Specifies the types of secondary and single-table join indexes that are to be considered for analysis.
Consider All Index Types	Click to consider all index types during index analysis.
Include Index Types	Click to consider only certain index types during index analysis, and then click Select Index Types.
Exclude Index Types	Click to avoid certain index types during index analysis, and then click Select Index Types.
Select Index Types	Select to choose the i index types to be used. The Index Types dialog box opens. Note: Only single-table join indexes are included in the analysis for choices 5 and 6. Hash indexes are not supported.
Set Defaults	Restores the default values for the fields in the dialog box.