DB2 – Fetch 1 Row Vs Optimize 1 Row Top Differences

These are few important clauses you can use in SELECT statement. Those are Fetch, Optimize, Queryno and how to override isolation level.

FETCH FIRST 1 ROW ONLY:  Will limit the number of rows from the select statement. It avoids unnecessary I/O for select statements.

Similarly you can use FETCH FIRST 10 ROWS ONLY.

OPTIMIZE FOR 2 ROWS: This is little different from previous one. The performance applies only for 2 rows. For the rest of the rows degradation of performance takes place.

Related Posts: Download 238 SQL queries 

QUERYNO clause specifies the number to be used for this SQL statement in EXPLAIN output and trace records.


How to override Isolation in select statement

You can use WITH UR

Author: Srini

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