DB2 Fetch ideas on how to optimize result

After I returned from my work, I got an idea to write a post on Fetch. I am writing it by listening to a popular mind music.

FETCH FIRST n  ROW/ROWS ONLY

Default is ‘1’. It improves performance, if we use this statement in SQL query.

Rules to use Fetch First

  • FETCH FIRST we should not use with FOR UPDATE
  • If FETCH FIRST and ORDER BY clauses are used, first Order By will be applied on the entire table, then it gives the Fetch First n rows.
  • FETCH FIRST m ROWS ONLY and OPTIMIZE FOR n ROWS , if both mentioned,  m>n, then sql optimizes for n rows.

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.