SET OPTIMIZATION

Use the SET OPTIMIZATION statement to specify how much time the optimizer spends developing a query plan or specifying optimization goals.

This statement is an extension to the ANSI/ISO standard for SQL.

Syntax

>>-SET OPTIMIZATION--+-+-HIGH-+--------------+-----------------><
                     | '-LOW--'              |
                     |  (1)                  |
                     '--------+-FIRST_ROWS-+-'
                              '-ALL_ROWS---'

Notes:

Dynamic Server only

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 Dynamic Server, 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.