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

LNAME CITY FNAME CNO

:::::::::::::: :::::::::::::: :::::::::::::: :::::::::::::::
JONES LISBON MARIA 2026477

JONES LONDON DAVID 5643234

JONES LONDON MIKE 1234567

JONES LONDON TED 0037378

JONES MADRID ADAM 0968431

:::::::::::::: :::::::::::::: :::::::::::::: :::::::::::::::

JONSON BRUSSELS INGA 3620551

JONSON LONDON DAVID 6643234

JONSON MILAN SOPHIA 2937633

:::::::::::::: :::::::::::::: :::::::::::::: :::::::::::::::

Figure 6.1 Multiple thin index slices (CURSOR62) if MC = 2.

SQL 6.2

DECLARE CURSOR62 CURSOR FOR
SELECT CNO, FNAME

FROM CUST

WHERE LNAME =:LNAME

AND
CITY BETWEEN :CITY1 AND :CITY2

ORDER BY FNAME

Now even a DBMS without a perfect optimizer will read several thin slices
from the index (LNAME, CITY, FNAME, CNO). EXPLAIN will report MC = 2
for CURSOR62. The response time will depend on the number of slices and the
number of rows in each slice.
Let's compare the two alternatives using the following assumptions:

ž One million rows in the customer table
ž Worst-case filter factor for LNAME = 1%
ž Worst-case filter factor for CITY = 10%

The index slice scanned by CURSOR61 will consist of 10,000 rows (MC = 1;

1% of 1 , 000 , 000 = 10 , 000); only 10% of these will be for the required CITY

(1000 rows).