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

Example5.3:NonclusteringIndexAccess

SQL 5.3

DECLARE CURSOR53 CURSOR FOR
SELECT CNO, LNAME, FNAME

FROM CUST

WHERE ZIP = :ZIP

AND
LNAME = :LNAME

ORDER BY FNAME

If the table rows are not accessed via the clustering index as in Figure 5.5, the
1000 table touches are now random.

Index ZIP, LNAME, FNAME TR = 1 TS = 1000

Table CUST TR = 1000 TS = 0

Fetch 1000 × 0.1 ms

LRT TR = 1001 TS = 1000

1001 × 10 ms 1000 × 0.01 ms
10s+10ms+100ms=10s

Adding CNO to the index to make it a three-star index is now an excellent
idea because it would reduce the local response time from 10 to 0.1 s.

ZIP LNAME FNAME

T

30103 JONA MARIA

T

30103 JONES ADAM
30103 JONES ANNE

T

T T T T T T T T

T

T

30103 JONES ZAK

T

30103 JONI BETTY

T

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

Figure 5.5 Nonclustering index access.