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. 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. 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.
|
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. |