Proactive Index Design - Relational Database Index Design and the Optimizers - page 77

LNAME FNAME

CITY

T

BERN

MARIA

JONA

T T

OSLO

JONES
JONES

ADAM
ADAM

T

T T T T

T

JONES ZUG

T

T

JONI LONDON

BETTY

1,000,000 index rows 1,000,000 table rows

Figure 5.8 Cheapest adequate index or best possible index: Example 1B.

We will use the BQ to determine whether the index (LNAME, FNAME) will
provide adequate performance for CURSOR55 (SQL 5.5) (Fig. 5.8). If necessary,
we will then estimate the local response time for a transaction that FETCHes all
the result rows. The assumptions are:
1. There are one million table rows.
2. The only suitable index is (LNAME, FNAME).
3. The maximum filter factor for predicate LNAME = : LNAME is 1%.
4. The maximum filter factor for predicate CITY : CITY is 10%.

5. The maximum size of the result table is 1000 rows (10% of 1% of
1,000,000).
6. The table rows are stored in CNO order [primary key index (CNO) is the
clustering index or the table is frequently unloaded and reloaded after a
sort by CNO].

=

SQL 5.5

DECLARE CURSOR55 CURSOR FOR
SELECT CNO, FNAME

FROM CUST

WHERE LNAME = :LNAME

AND
CITY = :CITY

ORDER BY FNAME