Indexing for Table Joins - Relational Database Index Design and the Optimizers - page 154

FF(CCTRY = :CCTRY) = 10%

STAR T

200 T

200 T

CCTRY,
CNO,
CNAME,
CTYPE

IEUR
DESC ,
INO,
CNO

U U

P,C F,C

P

CNO INO CNO

CUST INVOICE

1,000,000 rows 20,000,000 rows

Figure 8.9 Program B + with ideal indexes and one screen per transaction.

using either cursor, the program scans a 200-row slice of index (IEUR DESC,
INO, CNO) because if FF(CCTRY :CCTRY) is 10%, every tenth invoice, on

average, is associated with the specified country. This step takes 20

=

/ 0 . 1 = 200

touches. Only the first of these is a random touch.

Index IEUR DESC, INO, CNO TR = 1 TS = 199

Fetch 20 × 0.1 ms

LRT TR = 1 TS = 199

1 × 10 ms 199 × 0.01 ms
10ms+2ms+2ms=14ms

Step 2: Read One Index Row (CCTRY, CNO, CNAME, CTYPE) for Each
Large Invoice As the customer numbers are not consecutive, the DBMS must
do 200 random touches.

Index CCTRY, CNO, CNAME, CTYPE TR = 200

Fetch 200 × 0.1 ms

LRT TR = 200

200 × 10 ms
2s+20ms=2s

Local Response Time

14 ms + 2s = 2s