Databases Reference
In-Depth Information
Step 3.
Select TEMPB where city = 'NY' forming TEMPC (same attributes as TEMPB)
at 73 B per row. Assume NY has 10% of all suppliers.
Number of block accesses (step 3)
= read TEMPB + write TEMPC
= 488 + 49 = 537.
Step 4.
Project TEMPC over pname forming RESULT (pname) at 10 B per row.
Number of rows = 10K (read TEMPC)
Number of block accesses (step 4)
= read TEMPC
= 49.
In summary, for the entire query (option 1A) we have the following totals:
Number of block accesses for query (option 1A)
= 563 + 5,091 + 537 + 49 = 6,240.
Option 1B Cost Estimation: Do All Selections (Including Projections) before Joins,
without Indexes (and an Exploration of the Potential Use of Indexes)
Row
Size
No.
Rows
Blocks to
Scan Table
Ta b l e
BF
supplier (S)
37 B
200
405
1
part (P)
23 B
100
652
1
shipment (SH)
26 B
100K
576
174
TEMP1 (select from S)
37 B
20
405
1
TEMP2 (project over SH)
13 B
100K
1,153
87
TEMP3 (project over P)
18 B
100
833
1
TEMP4 (TEMP1 semi-join TEMP2)
13 B
10K
1,153
9
TEMP5 (TEMP4 semi-join TEMP3)
18 B
10K
833
13
This approach is detailed below and summarized in Figure 3.2.
Step 1
.
We first select S where city = 'NY' forming TEMP1 (snum, sname, city, status)
at 37 B per row. Because this is a very small table, we avoid creating an index and just
scan the table instead.
