DBzTech-Technology Dossier

Two SQL clauses that often cause confusion are FETCH FIRST N ROWS ONLY and OPTIMIZE FOR N ROWS. While they may sound similar, their purposes are different.

1. FETCH FIRST N ROWS ONLY

• Purpose: Limits the number of rows returned by the query.
• Example: FETCH FIRST 5 ROWS ONLY returns exactly 5 rows, even if more qualify.
• Use Case: Pagination, top-N queries, or when only a fixed number of rows are needed.

2. OPTIMIZE FOR N ROWS

• Purpose: Influences the optimizer to prioritize fast retrieval of the first N rows.
• Example: OPTIMIZE FOR 5 ROWS still returns all qualifying rows but retrieves the first few faster.
• Use Case: Interactive applications where users view a small number of rows at a time, allowing incremental processing.

Example Scenario

Suppose an online app displays 5 customer records per screen:

SELECT fname, lname, dept, sal, rollno
FROM dept
ORDER BY sal DESC;

• Index exists on rollno and a descending index on sal (with low clustering).
• Without OPTIMIZE FOR, DB2 may perform a tablespace scan + sort on sal.
• With OPTIMIZE FOR 5 ROWS, DB2 uses the sal index directly to deliver the top 5 rows quickly. Subsequent rows can be fetched incrementally.

Side-by-Side Comparison

✅ Key Takeaway:

• Use FETCH FIRST when you only want a subset of rows.
• Use OPTIMIZE FOR when you need fast access to initial rows without limiting the full result set.