Database Reference
In-Depth Information
Together with the FETCH cost, the LRT for this step will be 20 s.
Step 2: Access Table INVOICE via Clustering Index CNO When a cus-
tomer from the specified country is obtained from the customer table, all the
invoices for that customer must be checked to find the large invoices. These
index rows (on average, 20 per customer) are next to each other, as are the table
rows because index CNO on table INVOICE is the clustering index. It is impor-
tant to have consistent clustering in large related tables like CUST and INVOICE.
It takes 21 index touches and 20 table touches to check all the invoices of an
average customer, but only the first index touch and the first table touch are ran-
dom. If 100,000 customers (worst input: 10% of customers) are processed, the
touches will be as shown below. Note that the customer number values the pro-
gram moves from FETCH CUSRSORC to OPEN CURSORI are not consecutive;
each one causes one random index touch and one random table touch.
TS = 100,000 × 20
Index CNO
TR = 100,000
Table INVOICE
TR = 100,000
TS = 100,000 × 19
Fetch 100,000 × 0.1ms
LRT
TR = 200,000 TS = 3,900,000
200,000 × 10 ms 3,900,000 × 0.01 ms
2000s+39s+10s=2050 s
According to the QUBE, this step contributes over twice as much as the first step
if an index scan had indeed been chosen; a table scan in step 1 would have been
insignificant compared to the cost of the inner table processing.
Step 3: Sort the Result Rows With the worst input the result table consists
of 0 . 1 × 0 . 001 × 20 , 000 , 000 = 2000 large orders. This must be displayed in
descending order by invoice total (IEUR). The sort will take place as an additional
step at the end, perhaps using a temporary table. According to our estimates, the
CPU time for the sort is about 0.01 ms per row; 0.02 s for 2000 rows. Such a
small sort will probably not cause any disk I/O, so the elapsed time is roughly
0.02 s as well. The QUBE assumes that the sort time is absorbed in the FETCH
cost because it is very small and so can be ignored.
Local Response Time With the worst input, the local response time with this
access path is the sum of the three components:
20 s + 2050 s + 0s = 2070 s = 35 min
Over half an hour to wait for the first screen! Even if the users were to be told
to take a lunch break after entering the required country input, the disk traffic
burst, up to 200,000 random reads in half an hour, could cause noticeable drive
queuing for other concurrent users.
With the current indexes as shown in Figure 8.5, the DBMS must scan the
whole INVOICE table looking for large invoices. When one is found, it checks
Search WWH ::




Custom Search