The SQL Optimizer finds all the alternative SQL statements that produce the equivalent results to your SQL. These statements may run faster than your source. They also may run longer than your source. Therefore, you will want to terminate the longer running SQL statements. The SQL Termination page is used to set the termination criteria for each SQL statement while retrieving the run time. If the current run time for a particular SQL statement exceeds the termination time, that SQL statement 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 Original SQL option enables you to retrieve the run time of SQL statements that run faster than the original SQL statement. It terminates all SQL statements that run longer than the run time from the original SQL. If you choose this option, the original SQL statement is automatically selected on the Selected SQL tab and during the Batch Run, you cannot terminate the original SQL because its run time is needed to determine when to terminate the SQL alternatives.
The Best Running Time SQL option allows you to retrieve the run time of SQL statements that run faster than the current best run time. With this option, the first SQL statement is run and the time from that statement is used as the termination time. When a SQL statement runs faster than this time, 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 SQL statement.
Note: The first SQL statement is either the original or the SQL with the lowest Estimated I/O cost. This depends on whether the Always run Original SQL first option is checked on the Selected SQL tab.
The User Defined Time option retrieves the run time of the SQL statements that are less than the defined time. If your original statement takes a long time to execute and there are many alternative statements, executing all statements may take considerable time. In that event, consider setting an aggressive user defined termination time. If the original SQL takes 1 hour, try a 5-minute termination time. If no alternative statements execute in under that period, raise the termination time to 10 minutes, etc.
You can also combine User Defined Timewith Original SQLor 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. All SQL statements run to completion.
© ALL RIGHTS RESERVED. Feedback Terms of Use Privacy Cookie Preference Center
