The Index Advisor provides several index candidates for the SQL statement. The SQL statement will run faster with some of these index alternatives and it may run longer with other index alternatives. Therefore, you will want to terminate SQL statement when it is running longer. The SQL Termination tab is used to set the termination criteria for the SQL statement with each index set. If the current run time for the SQL statement exceeds the termination time, then it is terminated automatically.
Note: The termination time has a percentage delay added to it.
To define your termination criteria select one of the following options
The Used Index option enables you to retrieve the run time of SQL statements that run faster than the original SQL statement. It terminates the SQL statement when it runs longer using an Index alternative than it ran with the current or "used" indexes. If you choose this option, the Used Index is automatically selected on the Selected Index Set tab. During the Batch Run, you cannot terminate the SQL statement when it is running with the Used Indexes because this run time is needed to determine when to terminate the Index alternatives.
The Best running time SQL option allows you to retrieve the run time of the SQL statement that is faster than the current best run time. With this option, the run time from the first execution of the SQL statement is used as the termination time. When a faster run is found, the faster time is used as the new termination time. So you are always using the current fastest run time as the termination time for the next execution.
Note: The first index is either the Used Index or the index with the lowest Est. I/O Cost. This depends on whether the Always run Used Index first option is checked on the Selected Index Set tab.
The User-defined time option retrieves the run time of the SQL statement when it is less than the defined time.
You can also combine User defined time with Used Index or Best running time SQL by clicking the Or user-defined time checkbox next to each one.
The Run without termination option allows you to retrieve the run time of the SQL statements without any termination criteria. The SQL statement is run to completion with each Index Set.
© ALL RIGHTS RESERVED. Feedback Terms of Use Privacy Cookie Preference Center
