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.

QUERYNO=999

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.