Informix Guide to SQL: Syntax SQL Statements

Use the SET OPTIMIZATION statement to specify the time the optimizer spends to determine the query plan or to specify the optimization goals of the query.

You can execute a SET OPTIMIZATION statement at any time. The optimization level carries across databases on the current database server.

When you issue a SET OPTIMIZATION statement, the option that you specify is persistent. That is, the new optimization level remains in effect until you issue another SET OPTIMIZATION statement or until the program ends.

The default database-server optimization level for the time the optimizer spends determining the query plan is HIGH.

The default database-server optimization level for the optimization goal of the query is ALL_ROWS.

Although you can set only one option at a time, you can issue two SET OPTIMIZATION statements: one that specifies the time the optimizer spends to determine the query plan and one that specifies the optimization goal of the query.

The HIGH and LOW options relate to the time the optimizer spends to determine the query plan:

• HIGH

This option directs the optimizer to use a sophisticated, cost-based algorithm that examines all reasonable query-plan choices and selects the best overall alternative.

For large joins, this algorithm can incur more overhead than you desire. In extreme cases, you can run out of memory.

• LOW

This option directs the optimizer to use a less sophisticated, but faster, optimization algorithm. This algorithm eliminates unlikely join strategies during the early stages of optimization and reduces the time and resources spent during optimization.

When you specify a low level of optimization, the database server might not select the optimal strategy because the strategy was eliminated from consideration during the early stages of the algorithm.

The FIRST_ROWS and ALL_ROWS options relate to the optimization goal of the query:

• FIRST_ROWS

This option directs the optimizer to choose the query plan that returns the first result record as soon as possible.

• ALL_ROWS

This option directs the optimizer to choose the query plan which returns all the records as quickly as possible.

You can also specify the optimization goal of a specific query with the optimization-goal directive. For more information on how to use a directive to specify the optimization goal of a query, see Optimizer Directives.

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 SPL routine. This step stores the best query plans for the SPL routine. Then execute a SET OPTIMIZATION LOW statement before you execute the SPL routine. The SPL routine then uses the optimal query plans and runs at the more cost-effective rate.

The following example shows optimization across a network. The central database (on the midstate database server) is to have LOW optimization; the western database (on the rockies database server) is to have HIGH optimization.

CONNECT TO 'central@midstate';

SET OPTIMIZATION LOW;

SELECT * FROM customer;

CLOSE DATABASE;

CONNECT TO 'western@rockies';

SET OPTIMIZATION HIGH;

SELECT * FROM customer;
CLOSE DATABASE;

CONNECT TO 'wyoming@rockies';

SELECT * FROM customer;

The wyoming database is to have HIGH optimization because it resides on the same database server as the western database. The code does not need to respecify the optimization level for the wyoming database because the wyoming database resides on the rockies database server like the western database.

The following example directs the optimizer to use the most time to determine a query plan and to then return the first rows of the result as soon as possible.

SET OPTIMIZATION LOW;
SET OPTIMIZATION FIRST_ROWS;
SELECT lname, fname, bonus
FROM sales_emp, sales
WHERE sales.empid = sales_emp.empid AND bonus > 5,000
ORDER BY bonus DESC

Related statements: SET EXPLAIN and UPDATE STATISTICS

For information on other methods by which you can alter the query plan of the optimizer, see Optimizer Directives.

For more information on how to optimize queries, see your Performance Guide.