0-15

9

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 options.

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.

For more information about the QueryBypassed connection property, see Accessing connection property values.

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.

• INSERT statement
• UPDATE statement
• DELETE statement
• SELECT statement
• UNION statement
• EXCEPT statement
• INTERSECT statement
• How the optimizer works
• optimization_level connection property