Optimization levels
The current optimization level set in an SPL routine affects
how the SPL routine is optimized. The SQL statement, SET OPTIMIZATION,
sets the optimization level, which in turn determines the algorithm
that the query optimizer uses, as follows.
| SET OPTIMIZATION statement | Algorithm used |
|---|---|
| SET OPTIMIZATION HIGH | Invokes a sophisticated, cost-based strategy that examines all reasonable query plans and selects the best overall alternative For large joins, this algorithm can incur more overhead than desired. In extreme cases, you can run out of memory. |
| SET OPTIMIZATION LOW | Invokes a strategy that eliminates unlikely join strategies during the early stages, which reduces the time and resources spent during optimization However, when you specify a low level of optimization, the optimal strategy might not be selected because it was eliminated from consideration during early stages of the algorithm. |
For SPL routines that remain unchanged or change only
slightly, you might want to set the SET OPTIMIZATION statement to HIGH when
you create the routine. This optimization level stores the best query
plans for the routine. Then set optimization to LOW before
you execute the routine. The routine then uses the optimal query plans
and runs at the more cost-effective rate if reoptimization occurs.