SET OPTIMIZATION statement

Use the SET OPTIMIZATION statement to specify how much time the query execution optimizer spends developing a query plan or specifying optimization goals. The SET OPTIMIZATION statement is an extension to the ANSI/ISO standard for SQL

When you use DB-Access with Informix®, the ENVIRONMENT options to the SET OPTIMIZATION statement can define a general optimization environment for all statements in the current session.

Syntax

^{(explicit id sop002)
sop002} ^{(explicit id sop003)
sop003} SET OPTIMIZATION { { HIGH | LOW } | { { FIRST_ROWS | ALL_ROWS } } | ^[]<ENVIRONMENT Options> ^[] }

Usage

You can execute a SET OPTIMIZATION statement at any time. The specified optimization level carries across databases on the current database server. The option that you specify remains in effect until you issue another SET OPTIMIZATION statement or until the program ends. The default database server optimization level for the amount of time that the query optimizer spends determining the query plan is HIGH.

On Informix®, the default optimization goal 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.

Similarly, you can issue multiple SET OPTIMIZATION statements that include the ENVIRONMENT Options clause to specify a session environment for optimizing queries. In data warehousing applications, an appropriate optimizer environment can improve the performance of join queries of tables in a star schema. Optimizer environment settings persist until another SET OPTIMIZATION ENVIRONMENT statement overrides them, or until the session ends. For more information, see the ENVIRONMENT Options topic.

Examples

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;

Here 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 re-specify 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 Informix® 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;

¹ DB-Access only

² See ENVIRONMENT Options