Factors Affecting the Index Design Process - Relational Database Index Design and the Optimizers - page 96

SQL 6.8

DECLARE CURSOR68 CURSOR FOR
SELECT CNO, FNAME
FROM CUST
WHERE LNAME = :LNAME
AND
CITY = :CITY
ORDER BY FNAME
WE WANT 20 ROWS PLEASE

OPEN CURSOR CURSOR610
FETCH CURSOR CURSOR610 max 20
CLOSE CURSOR CURSOR610

In this example, the user chooses both the last name (LNAME) and the
city (CITY) from pop-up windows before starting the transaction, which then
opens CURSOR68 (refer to SQL 6.8). When we analyzed this transaction earlier,
the whole of the result set was required. In this transaction, however, no more
than 20 FETCH calls are issued by the program , perhaps just enough to fill one
screen. The next transaction displays the next 20 result rows and so forth. This
technique complicates programming but improves the response time for the first
screen when the result table is very large. As before, the maximum filter factors
are assumed to be 1% (LNAME) and 10% (CITY).
Figure 6.3 shows the current indexes for the customer table. All of them fail
the BQ test:
Is there an existing or planned index that contains all the columns referenced
by the WHERE clause (a semifat index)?
How serious would the problem be now if we decided to use index (LNAME,
FNAME) anyway. The QUBE for the worst case will answer this question.

P,C U

LNAME,
FNAME

CNO PNO

CUST

Figure 6.3 Filter factor pitfall
example—indexes.

1,000,000 rows