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.

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

Mandalika

Updated on: 01-Dec-2020

751 Views

Kickstart Your Career

Get certified by completing the course

Get Started