Joinop Timeoutabort
SET JOINOP TIMEOUTABORT nnn specifies the time in milliseconds after which the optimizer stops considering query plans and uses the best plan found to that point. If no plan is found within the specified time, an error is generated.
If 0 (the default) is specified, the timeout is disabled.
Like SET JOINOP TIMEOUT nnn, SET JOINOP TIMEOUTABORT nnn instructs the optimizer to time out after nnn milliseconds of processing.
The TIMEOUTABORT option, however, also applies to the time prior to obtaining the first query plan. If the time expires and no plan is found, the query is aborted with an error. If at least one plan is found, the best plan is used and the query is executed.
Contrast this with SET JOINOP TIMEOUT nnn, where the time can expire only after the first plan is found.
As with SET JOINOP TIMEOUT, the default value of 0 for SET JOINOP TIMEOUTABORT effectively disables this feature.
If both TIMEOUT and TIMEOUTABORT have been assigned non-zero values, the following rules apply:
• If TIMEOUT is greater than or equal to TIMEOUTABORT, this is equivalent to TIMEOUT being set to 0, that is, its value is ignored and the TIMEOUTABORT mechanism is in effect.
• If TIMEOUTABORT is greater than TIMEOUT, then both timing strategies are in effect.
• If the TIMEOUT time expires, the search for a better plan stops if any plan is found.
• Otherwise, optimization continues until either a plan is found or the TIMEOUTABORT timer expires.
As with SET JOINOP TIMEOUT nnn, this new timing feature does not affect the default SET JOINOP TIMEOUT behavior, which is to time out when as much time has been used for optimization as the estimated execution time of the best plan found so far.
Last modified date: 01/30/2023