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

Step 2: Access index (IEUR DESC, CNO, INO)

Index IEUR DESC, CNO, INO TR = 1 TS = 0.1% × 20,000,000

LRT TR = 1 TS = 20,000

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

Step 3: Merge/Hash and FETCH the Result Rows If the results of steps 1
and 2 had required sorting into the join column sequence, CNO, the cost of step
3 would be twice the cost of the two initial scans:

Sort and Merge/Hash 2 ( 100 , 000 + 20 , 000 ) × 0 . 01 ms = 2 . 4s
The outer scan does , however, provide the rows in the required sequence; the
resulting cost is significantly reduced:
Sort and Merge

/ Hash 2 ( 20 , 000 ) × 0 . 01 ms = 0 . 4s

Fetch 2000 × 0 . 1ms = 0 . 2s

Note that this is true in this case only because the first column, CCTRY, is in an
equal predicate; with a range predicate, the index scan would not be accessing
the rows in CNO sequence.

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

1s + 0 . 2s + 0 . 6s = 1 . 8s

Conclusion:MS/HJwithIdeal Indexes

The merge scan figures have been added to the nested-loop comparison figures
in Table 8.2.

Table 8.2 Summary of the Local Response Times—2

Type Program A Program

B/C NLJ

Program C
MS/HJ

Current indexes 35 min 10 min 3.5 min

Ideal indexes 17 min 3.5 min 1.8 s

Ideal indexes, ideal program ( + ), multi-screen 2s

result

Ideal indexes, ideal program ( + ), single-screen 3.5 min

result (FF pitfall)

BJQ ideal indexes, ideal program ( + ), 0.2 s

single-screen result (FF Pitfall)