The SET OPTIMIZATION Statement

With Version 5.0 of the Informix products, optimisation may be set to a high or low level. The default is high level optimisation. This is a sophisticated, cost-based strategy examines all reasonable choices and selects the best overall alternative. However, for large joins, this may incur so much pre-processing overheads that the overall performance of the query is severely affected.

Low level optimisation eliminates unlikely join strategies during the early stages, thus reducing the amount of time and resources spent during optimisation. However, low le optimisation may eliminate the optimal strategy during these early stages.

In most cases, high level optimisation will result in better performance. Howevei experimentation with the low level may reveal that it is better for some applications.

To change the level of optimisation, use the following statement:

SET OPTIMIZATION [HIGH | LOW]

The level of optimisation will affect all databases on the present database server. The level of optimisation is set until the statement is issued, changing the level.