Optimizing retrieval for a small set of rows

When you need only a few of the thousands of rows that satisfy a query, you can tell Db2 to optimize its retrieval process to return only a specified number of rows.

About this task

Question: How can I tell Db2 that I want only a few of the thousands of rows that satisfy a query?

Start of change Answer: Use the optimize clause or the fetch clause of the SELECT statement. End of change

Db2 usually optimizes queries to retrieve all rows that qualify. But sometimes you want to retrieve a few rows. For example, to retrieve the first row that is greater than or equal to a known value, code you SELECT statement like the following:

SELECT column list FROM table
WHERE key >= value
ORDER BY key ASC

Even with the ORDER BY clause, Db2 might fetch all the data first and sort it after the fetch, which could impact performance. Instead, you can write the query in one of the following ways:

SELECT * FROM table
WHERE key >= value
ORDER BY key ASC
OPTIMIZE FOR 1 ROW

SELECT * FROM table
WHERE key >= value
ORDER BY key ASC
FETCH FIRST n ROWS ONLY

Use OPTIMIZE FOR 1 ROW clause to influence the access path. OPTIMIZE FOR 1 ROW tells Db2 to select an access path that returns the first qualifying row quickly.

Use FETCH FIRST n ROWS ONLY clause to limit the number of rows in the result table to n rows. FETCH FIRST n ROWS ONLY has the following benefits:

When you use FETCH statements to retrieve data from a result table, the fetch clause causes Db2 to retrieve only the number of rows that you need. This can have performance benefits, especially in distributed applications. If you try to execute a FETCH statement to retrieve the n+1st row, Db2 returns a +100 SQLCODE.
When you use fetch clause in a SELECT INTO statement, you never retrieve more than one row. Using fetch clause in a SELECT INTO statement can prevent SQL errors that are caused by inadvertently selecting more than one value into a host variable.

When you specify the fetch clause but not the optimize clause, the optimize clause is implicit. When you specify FETCH FIRST n ROWS ONLY and OPTIMIZE FOR m ROWS, and m is less than n, Db2 optimizes the query for m rows. If m is greater than n, Db2 optimizes the query for n rows.