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

CNO

0147002

0147003
0147005
0147006
0147007
0147009
0147012
0147014
0147015

T

T

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

Figure 5.3 Primary key index access.

Reading a table row through the primary key index requires one random
touch to the index and one random touch to the table.

Index CNO TR = 1

Table CUST TR = 1

Fetch 1 × 0.1 ms

LRT TR = 2

2 × 10 ms + 0.1 ms
=20ms

Example5.2:ClusteringIndexAccess

SQL 5.2

DECLARE CURSOR52 CURSOR FOR
SELECT CNO, LNAME, FNAME

FROM CUST

WHERE ZIP = :ZIP

AND
LNAME = :LNAME

ORDER BY FNAME

In Figure 5.4 let us assume we have 1000 Joneses in the area code (ZIP) 30103.
This two-star index for CURSOR52 accesses a thin index slice with two matching
columns; no sort is required as the index provides the required sequence; index
only is of course not possible.
How long will it take to scan the index slice of 1000 rows? It takes a random
touch to find the first index row in the slice and then the DBMS reads forward