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

DB2DatabaseBig Data AnalyticsSQL

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
raja
Published on 01-Dec-2020 04:45:48
Advertisements