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

Step 1: Full INVOICE Table Scan The INVOICE table has 20,000,000 rows.
Checking each of these takes 20,000,001 touches, including the touch for the
end-of-file mark. Only the first touch is random.

Table INVOICE TR = 1 TS = 20,000,000

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

LRT TR = 1 TS = 20,000,000

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

Step 2: Read the CUST Row Corresponding to Each Large Invoice via
Clustering Index CNO Using the assumed worst input filter factors, 0 . 001 ×
20 , 000 , 000 = 20 , 000 of the invoices are large. Therefore the DBMS will read
20,000 CUST rows via the CNO index. Only 10% of these belong to the required
country, and so most of the rows will be rejected, only 2000 being accepted.

Index CNO TR = 20,000

Table CUST TR = 20,000

Fetch 20,000 × 0.1 ms

LRT TR = 40,000

40,000 × 10ms+2s=400s

Step 3: Sort the Result Rows The 20,000 rows fetched in step 1 from
CURSORI must be sorted in descending order by invoice total, IEUR. Sorting
20,000 rows takes a negligible amount of time (roughly 200 ms), again assumed
to be absorbed in the FETCH cost.

Local Response Time With the worst input, the local response time with this
access path is the sum of the three components:

200 s + 400 s + 0s = 600 s = 10 min

Surprisingly, program B is considerably faster than program A, even with
the current indexes—no index for IEUR! How is this possible? There are two
reasons:

1. With current hardware, sequential read is really fast: A table with
20,000,000 rows may be scanned in about 3 min (based on the
QUBE figures).
2. Because the highest filter factor for the predicate IEUR :IEUR is only

0.1%, the number of random touches to the inner table is lower than with
program A.

<