optimize-clause

The optimize-clause tells Db2 to assume that the program does not intend to retrieve more than integer rows from the result table. Without this clause, Db2 assumes that all rows of the result table will be retrieved, unless the FETCH FIRST clause is specified. Optimizing for integer rows can improve performance. If this clause is omitted and the FETCH FIRST is specified, OPTIMIZE FOR integer ROWS is assumed, where integer is the value that is specified in the FETCH FIRST clause. Db2 will optimize the query based on the specified number of rows.

The clause does not limit the number of rows that can be fetched, change the result table, or change the order in which the rows are fetched. Any number of rows can be fetched, but performance can possibly degrade after integer fetches. In general, if you are retrieving only a few rows, specify OPTIMIZE FOR 1 ROW to influence the access path that Db2 selects.

The value of integer must be a positive integer (not zero).

Row access controls indirectly affects the OPTIMIZE FOR clause because row access controls affect the rows that are accessible to the authorization ID or role of the subselect.

Column access controls do not affect the OPTIMIZE FOR clause.