The optimization_level option controls the amount of effort that the SQL Anywhere optimizer spends on optimizing SQL data
manipulation language (DML) statements. This option controls the maximum number of alternative join strategies that the optimizer
will consider for any SELECT block. The higher the setting of optimization_level, the greater the maximum number of join strategies
that the optimizer will consider.
If the option is set to 0, then the SQL Anywhere optimizer chooses the first access plan it considers for execution, in effect
avoiding any cost-based comparison of alternative plans. In addition, with level 0 some semantic optimizations of nested queries
are disabled. If this option is set to a value higher than 0, the optimizer evaluates alternative strategies and chooses the
one with the lowest expected cost. If this option is set to a value greater than the default (9), the optimizer is more aggressive
in its search for alternative strategies, possibly resulting in much higher elapsed time spent in the optimization phase.
In typical scenarios, this option is temporarily set to lower levels (0, 1, or 2) when the application desires faster OPEN
times for a DML statement. It is known that although the statement may be complex, the query's execution time is very small,
and the specific access plan chosen by the optimizer is less consequential. It is not recommended that the PUBLIC setting
of optimization_level be changed from its default.
The effect of setting the optimization_level option is independent of the settings of the optimization_goal and optimization_workload
Simple DML statements (single-block, single-table queries that contain equality conditions in the WHERE clause that uniquely
identify a specific row) are optimized heuristically and bypass the cost-based optimizer altogether. The optimization of simple
DML statements is not affected by the setting of the optimization_level option. The count of the number of requests optimized
through the optimizer bypass mechanism is available as the QueryBypassed connection property.
You can override any temporary or public settings for this option within individual INSERT, UPDATE, DELETE, SELECT, UNION,
EXCEPT, and INTERSECT statements by including an OPTION clause in the statement.