Optimizers Are Not Perfect - Relational Database Index Design and the Optimizers - page 247

SQL 14.1 is a typical case where the ultimate solution is to split the cursor.
Replacing the OR with a UNION ALL would probably lead to an unnecessary
sort for an ORDER BY but omitting the ORDER BY would be quite risky; many
things may change in the future. To avoid the sort and all the redundant touches,
two cursors would have to be written as shown in SQL 14.2.
SQL 14.2
DECLARE CURSOR142A CURSOR FOR
SELECT LNAME, FNAME, CNO
FROM CUST
WHERE LNAME = :LNAMEPREV
AND
CNO > :CNOPREV
ORDER BY LNAME, CNO
WE WANT 20 ROWS PLEASE
EXPLAIN: MC = 2, index only, no sort
DECLARE CURSOR142B CURSOR FOR
SELECT LNAME, FNAME, CNO
FROM CUST
WHERE LNAME > :LNAMEPREV
AND
LNAME <= :LNAMEMAX
ORDER BY LNAME, CNO
WE WANT 20 ROWS PLEASE
EXPLAIN: MC = 1, index only, no sort
The application program first opens CURSOR142A and issues a maximum
of 20 FETCH calls. If this cursor runs out of result rows, it is closed and cursor
CURSOR142B is opened. The program keeps FETCHing until it runs out of
result rows or has filled a screen. Before terminating, the program saves the last
LNAME and CNO values for the next transaction.
This program does no unnecessary touches; it always starts at the index
position at which the previous transaction exited. According to the QUBE, the
local response time will be:
1 × 10 ms + 19 × 0.01 ms = 10 ms
Let us return one last time to the search for large men.
In this case, replacing the OR with a UNION would be a good solution
if the program FETCHes the whole result in one transaction, which seems to
be the case as there is no WE WANT n ROWS PLEASE. Now it is easy to
derive the ideal indexes for the two SELECTs: