Use the OPTIMIZE clause

If an application is not going to retrieve the entire result table for a cursor, using the OPTIMIZE clause can improve performance. The query optimizer modifies the cost estimates to retrieve the subset of rows using the value specified on the OPTIMIZE clause.

Assume that the following query returns 1000 rows:

EXEC SQL
    DECLARE C1 CURSOR FOR
    SELECT EMPNO, LASTNAME, WORKDEPT
      FROM CORPDATA.EMPLOYEE
      WHERE WORKDEPT = 'A00'
    ORDER BY LASTNAME
    OPTIMIZE FOR 100 ROWS
END EXEC.

Note: The values that can be used for the preceding OPTIMIZE clause are 1–9999999 or ALL.

The optimizer calculates the following costs.

The optimize ratio = optimize for n rows value / estimated number of rows in answer set.

Cost using a temporarily created index:
 
               Cost to retrieve answer set rows
            +  Cost to create the index
            +  Cost to retrieve the rows again
                  with a temporary index        * optimize ratio
 
Cost using a SORT:
 
               Cost to retrieve answer set rows
            +  Cost for SORT input processing
            +  Cost for SORT output processing  * optimize ratio
 
 
Cost using an existing index:
 
               Cost to retrieve answer set rows
               using an existing index          * optimize ratio

In the previous examples, the estimated cost to sort or to create an index is not adjusted by the optimize ratio. This method allows the optimizer to balance the optimization and preprocessing requirements.

If the optimize number is larger than the number of rows in the result table, no adjustments are made to the cost estimates.

If the OPTIMIZE clause is not specified for a query, a default value is used based on the statement type, value of ALWCPYDTA, or output device.

Table 1. OPTIMIZE FOR n ROWS default value
Statement Type	ALWCPYDTA(*OPTIMIZE)	ALWCPYDTA(YES or NO)
DECLARE CURSOR	The number or rows in the result table.	30 rows or the number of rows in the result table.
Embedded Select	2	2
INTERACTIVE Select output to display	30 rows or the number of rows in the result table.	30 rows or the number of rows in the result table.
INTERACTIVE Select output to printer or database table	The number of rows in the result table.	The number of rows in the result table.

The OPTIMIZE clause influences the optimization of a query:

To use an existing index (by specifying a small number).
To enable the creation of an index, or run a sort or hash by specifying many possible rows in the answer set.