Additional Estimation Considerations - Relational Database Index Design and the Optimizers - page 283

Alternative2withMergeScan

Step 1: Access index (CCTRY, CNO, CNAME, CTYPE)
TR = 1 TS = 100,000
CPU time = 100 µ s × 1+5 µ s × 100,000 = 500 ms
Step 2: Access index (IEUR DESC, CNO, INO)
TR = 1 TS = 20,000
CPU time = 100 µ s × 1+5 µ s × 20,000 = 100 ms
Step 3: Merge and FETCH the result rows

The outer scan on CUST provides the rows in the required sequence. There-
fore, the resulting CPU time is

CPU time for Sort and Merge: 2 × 20 , 000 × 0 . 01 ms = 0 . 4s
CPU time for Fetch: 2000 × 0 . 1ms = 0 . 2s

CPU Time With the worst input, the CPU time with this access path is the
sum of the three components:

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

Alternative2withHashJoin

Steps 1 and 2 are the same as with merge scan—CPU time = 0 . 5s + 0 . 1s =
0 . 6s

Step 3: Match with hashing and FETCH the result rows

The optimizer hopefully chooses to start by building a hash table for the
20,000 short rows from the INVOICE table. The size of that table may be
20

6 MB. This is small enough to load into memory in one
go, but too large to stay in a 1-MB CPU cache in a production environment.
After the hash table has been built, the CUST index slice is scanned (esti-
mated in step 1). The matching requires 100,000 random touches to the hash
table; the average CPU time per touch depends on the CPU cache hit ratio.
Given a 1-MB CPU cache, let us assume a range of 1 to 50

, 000 × 30 bytes = 0 .

s for the CPU time
per hash table touch. Now the CPU time for 100,000 hash table touches is

µ

100 , 000 × ( 1 ... 50 µ s ) = 0 . 1s ,..., 5s
Finally the result rows are obtained:

CPU time for Fetch 2 , 000 × 0 . 1ms = 0 . 2s

If the hashing cost is high, the optimizer should use merge scan, as the CPU time
estimate for the sort/merge phase was 0.4 s. An appropriate MERGE hint could
be used in that case, if necessary.