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

SQL 8.10

Program B

DECLARE CURSORI CURSOR FOR
SELECT CNO, INO, IEUR

FROM INVOICE

WHERE IEUR > :IEUR

ORDER BY IEUR DESC

OPEN CURSORI
DO

FETCH CURSORI while IEUR > :IEUR

SELECT CNAME, CTYPE

FROM CUST

WHERE CNO = :CNO

AND
CCTRY = :CCTRY

DO END
CLOSE CURSORI

Step 1: Read the Slice (IEUR :IEUR) from Index (IEUR DESC, INO, CNO)

The index slice contains 0 . 001 × 20 , 000 , 000 = 20 , 000 index rows. Thus, the
DBMS must do one random touch and 20,000 sequential touches. The major
cost in this step is now the FETCH processing; not as large as in program A but
still 2 s. The index provides the result set in the desired sequence without the
need for a sort.
Index IEUR DESC, INO, CNO

>

TR = 1 TS = 20,000

Fetch 0.1% × 20,000,000 = 20,000 × 0.1 ms

LRT TR = 1 TS = 20,000

1 × 10 ms 20,000 × 0.01 ms
10ms+0.2s+2s=2s

Step 2: Read One Index Row (CCTRY, CNO, CNAME, CTYPE) for Each
Large Invoice As the required customer numbers are not consecutive, the
DBMS must do 20,000 random touches. Only 10% of these will be for the
appropriate country.

Index CCTRY, CNO, CNAME, CTYPE TR = 20,000

Fetch 20,000 × 0.1 ms

LRT TR = 20,000

20,000 × 10 ms
200s+2s=202s

Local Response Time

2s + 202 s = 3 . 5min