Database Reference
In-Depth Information
(the number of customers with a specified CCTRY value) and the number of
sequential touches is 2000 (the number of large invoices for that country).
Index CNO, IEUR DESC, INO TR = 100,000
TS = 2000
Fetch 100,000 × 0.1 ms
LRT TR = 100,000 TS = 2000
100,000 × 10 ms 2000 × 0.01 ms
1000 s + 0.02 s + 10 s = 1000 s
The FETCH calls take a further 10 s, but this now becomes insignificant compared
to the TRs against the index.
Local Response Time Ignoring the sort cost as before
11 s + 1000 s = 1011 s = 17 min
It is extremely important to appreciate that in this case the benefit of using an ideal
index is minimized because of the huge number of TRs to the inner table index.
ProgramB:OuterTableINVOICE
The ideal indexes for program B are not the same as the ideal indexes for program
A because the information used to access the two tables is now different.
Candidate A for CURSORI is now (IEUR DESC, INO, CNO). Candidate
A for the customer table is (CCTRY, CNO, CNAME, CTYPE). The CNO col-
umn comes from CURSORI. They are both three-star indexes and are shown in
Figure 8.8; there is no need to consider candidate B.
FF(IEUR > :IEUR) = 0.1%
START
20 KT
20 KT
CCTRY,
CNO,
CNAME,
CTYPE
U
U
IEUR
DESC ,
INO,
CNO
F,C
P,C
P
INO
CNO
CNO
CUST
INVOICE
1,000,000 rows
20,000,000 rows
Figure 8.8 Program B with ideal indexes.
Search WWH ::




Custom Search