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

Semifat Fat Best 1 2 3

***

QUBE (Worst input)

Index

LRT CPU

LNAME, FNAME 100 s 1s

LNAME, FNAME, CITY 0.2 s 54 ms

1

LNAME, FNAME, CITY, CNO 0.1 s 50 ms

2

Figure 15.5 Save CPU time
with a three-star index.

LNAME, CITY, FNAME, CNO 0.01 s 2 ms

3

100 KT

20 KT

CCTRY,
CNO,
CNAME,
CTYPE

IEUR DESC ,
CNO,
INO

MS/HJ

P,C

P

CNO INO

CUST INVOICE

1,000,000 rows 20,000,000 rows

Figure 15.6 MS/HJ CPU time.

The response time with alternative 2 (the second finalist) was quite good:
1.8 s for the worst input that produced a large result, 1000 rows. The only issue
with this alternative is the CPU time; after all, scanning the two index slices
requires 120,000 touches and then 20 , 000 + 100 , 000 rows had to be matched
either by sort/merge or by hashing; refer to Figure 15.6. Before making a decision
it would be sensible to make an estimate of the CPU times for alternative 2 to
make quite sure.
As a comparison, the CPU time per screen for alternative 1 is very short:

TR = 1 + 20 = 21 , TS = 20 + 0 = 20 , F = 20 , no sort

Thus the CPU time = 21 × 100 s + 20 × 5 s + 20 × 100 s = 4ms (per

µ µ µ

screen).