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

FF(CCTRY = :CCTRY)
= 10%

P,C

CNO CCTRY

P F,C

INO

CNO

CUST

1,000,000 rows

INVOICE

20 invoices
per average customer

20,000,000 rows

Figure 8.14 Current indexes for the case study.

Step 3: Merge/Hash and FETCH the Result Rows The 100,000 rows
resulting from step 1 would have to be sorted into CNO sequence. The 20,000
rows resulting from step 2 would also have to be sorted into CNO sequence. The
two sorted tables would then be merged. Taking a common 0.01 ms for each row
involved in the sort and merge process:
Sort and Merge / Hash 2 ( 100 , 000 + 20 , 000 ) × 0 . 01 ms = 2 . 4s
Fetch 2

, 000 × 0 . 1ms = 0 . 2s

Local Response Time With the worst input, the local response time with this
access path is the sum of the three components:
10 s + 200 s + 2 . 6s = 200 s = 3 . 5min

Conclusion:MS/HJwithCurrentIndexes

This is only one third of the LRT achievable with a nested-loop join using
the current indexes, but it is certainly not adequate with the worst input using
the MS/HJ. As usual, we can now design the best affordable indexes, probably
starting by designing and evaluating the ideal indexes.

Ideal Indexes

Candidate A for the CUST index access is (CCTRY, CNO, CNAME, CTYPE).
This is a three-star index; the access path provides the rows in the join column
sequence, and so the rows would not need to be sorted. Candidate A for the
INVOICE index access is (IEUR DESC, CNO, INO). This has only two stars
because the range predicate means that the access path cannot provide the rows