SQL Processing - Relational Database Index Design and the Optimizers - page 35

combinations, such as the N most common values together with the number of
rows for each value; many products (e.g., Oracle, SQL Server, and DB2 for
LUW) may also allow value distributions to be collected as histograms (N% of
rows occur in a user-defined number of key ranges).

Helping the Optimizer (Number of FETCH Calls)

When cost-based optimizers estimate the cost of alternative access paths, they
assume that all the result rows are required (FETCHed), unless they are informed
otherwise. If the whole result set is not going to be required, we can indicate
that we are only interested in the first N result rows. This is done in SQL Server
by adding:

OPTIONS (FAST n)

at the end of the SELECT statement.
With Oracle, an access path hint is used:

SELECT/*+ FIRST ROWS(n)*/

[the (n) option is only available with Oracle 9i and above].
The syntax for DB2 for z/OS is

OPTIMIZE FOR n ROWS

Examples are shown in SQL 3.2 S, O and D below.

SQL 3.2S

DECLARE LASTINV CURSOR FOR
SELECT INO, IDATE, IEUR
FROM INVOICE
WHERE CNO = :CNO
ORDER BY INO DESC
OPTIONS (FAST 1)

SQL 3.2O

DECLARE LASTINV CURSOR FOR
SELECT /*+ FIRST ROWS(1)*/
INO, IDATE, IEUR

FROM INVOICE

WHERE CNO = :CNO

ORDER BY INO DESC