optimize-for-clause

If this clause is omitted, it is assumed that all rows of the result table will be retrieved; if it is specified, it is assumed that the number of rows retrieved will probably not exceed n, where n is the value of integer. The value of n must be a positive integer (not zero). Use of the OPTIMIZE FOR clause influences query optimization, based on the assumption that n rows will be retrieved. In addition, for cursors that are blocked, this clause will influence the number of rows that will be returned in each block (that is, no more than n rows will be returned in each block). If both the fetch-clause and the optimize-for-clause are specified, the lower of the integer values from these clauses will be used to influence the communications buffer size. The values are considered independently for optimization purposes.

This clause does not limit the number of rows that can be fetched, or affect the result in any other way than performance. Using OPTIMIZE FOR n ROWS can improve performance if no more than n rows are retrieved, but might degrade performance if more than n rows are retrieved.

If the value of n multiplied by the size of the row exceeds the size of the communication buffer, the OPTIMIZE FOR clause will have no affect on the data buffers. The size of the communication buffer is defined by the rqrioblk or the aslheapsz configuration parameter.