What is the purpose of OPTIMIZE FOR ROWS in DB2 SQLs? How is it useful?

The OPTIMIZE FOR N ROWS is a DB2 clause which we can add in the query to give priority for the retrieval of the first few rows only. This clause will enable the optimizer to choose the access path that minimizes the response time for fetching first few rows.

The OPTIMIZE FOR N ROWS clause is not effective on SELECT DISTINCT and COUNT function because DB2 will need the entire qualifying rows in order to fetch the DISTINCT rows or COUNT the number of rows. The OPTIMIZE FOR N ROWS clause gives DB2 a better opportunity to establish the access path.

The OPTIMIZE FOR N rows clause can be used in a SQL query as given below.

Example

SELECT ORDER_ID, ORDER_TOTAL FROM ORDERS
   ORDER BY ORDER_TOTAL DESC
   OPTIMIZE FOR 2 ROWS

We will use “FETCH FIRST n ROWS ONLY” to limit the number of rows returned and it will not consider the actual number of qualifying rows.

A practical scenario in which we can use OPTIMIZE FOR N ROWS would be a CICS screen where we can display a list of 5 orders only.

Example

SELECT ORDER_ID, ORDER_TOTAL FROM ORDERS
   ORDER BY ORDER_TOTAL DESC
   OPTIMIZE FOR 5 ROWS

For example, our DB2 ORDERS table has the below data.

ORDER_ID	INVOICE_ID
IRN22345	Z451189
IRN89767	Z451189
IRN67331	Z955189
IRN56902	Z225184
IRN99781	Z896671
IRN09863	Z225184
IRN34119	Z094711
IRN10933	Z189112
IRN67114	Z009117
IRN31990	Z551891

 

The result of our OPTIMIZE FOR 5 ROWS query will be as given below.

ORDER_ID	INVOICE_ID
IRN99781	Z896671
IRN89767	Z451189
IRN67331	Z955189
IRN67114	Z009117
IRN56902	Z225184