This optional clause for a SELECT statement is currently syntactically supported only by the XDB Server. It may appear before or after any accompanying FOR FETCH ONLY clause.
On mainframe DB2 systems, the OPTIMIZE FOR clause allows you to specify how many retrieved rows are to be used when optimizing. If no row limit is specified with the OPTIMIZE FOR clause, the system optimizes according to the total number of rows actually fetched. If more rows are fetched than are specified in this clause, performance could degrade. If the same number or fewer rows are fetched than are specified in this clause, performance should improve. The OPTIMIZE FOR clause does not alter the result table or the order in which the rows are fetched.
Syntax
The OPTIMIZE FOR clause format appears below:
OPTIMIZE FOR integer { ROW | ROWS }
where:
| integer | Identifies the number of rows expected to be retrieved from the cursor. |
See also the OPTIMIZE directive in the precompiler manual for your compiler, and the OPTIMIZE FOR clause under the SELECT command in SQL Commands.