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