Deriving the Ideal Index for a SELECT - Relational Database Index Design and the Optimizers - page 56

Candidate B, however, may be much faster than candidate A if a program
fetches only sufficient rows to fill a single screen, such as CURSOR44 in SQL
4.4. As discussed in Chapter 3, if there is no sort in the access path, the DBMS
materializes the result rows FETCH by FETCH. This is why it is sometimes very
important to avoid sorting (by using candidate B). Producing the first screen with
a two-star candidate A index (requiring a sort) may take far too long if the result
table is large . We should always keep in mind that the result table may be
extremely large if the end user makes a typing error, for example.
The program using CURSOR44 will be very fast if there is no sort in the
access path (assuming the columns LNAME and CITY are the first two columns
of the index—in either order), even if the result table contains millions of rows.
Each transaction never makes the DBMS materialize more than 20 result rows.
We will later discuss how to implement the transaction that finds the next 20
result rows efficiently.
SQL 4.4
DECLARE CURSOR44 CURSOR FOR
SELECT CNO, FNAME
FROM CUST
WHERE LNAME = :LNAME
AND
CITY = :CITY
ORDER BY FNAME
WE WANT 20 ROWS PLEASE
OPEN CURSOR CURSOR4
FETCH CURSOR CURSOR4 ----- max 20 times
CLOSE CURSOR CURSOR4
IDEAL INDEX FOR EVERY SELECT?
Index design is quite straightforward if the best possible index is created for each
SELECT unless an identical index already exists. This process, essentially the
two-candidate algorithm described earlier, is mechanical and can be performed
automatically, given the following input:
1. The SELECT statement
2. Database statistics (number of rows, number of pages, column value dis-
tribution, etc.)
3. The worst-case filter factors for each simple predicate and certain com-
pound predicates
4. The current indexes
In the simplest approach, the current indexes are checked merely to avoid
identical twins. In one AS/400 system, which used this approach, the DBMS