Databases Reference
In-Depth Information
Number of block accesses (step 2)
= read SH + write TEMP2
= 174 + 87
= 261.
Step 3.
Next we project P over pnum, pname forming TEMP3 (pnum, pname) at 18
bytes per row. Again, no index is required for a projection operation.
Number of block accesses (step 3)
= read P + write TEMP3
= 1 + 1
= 2.
Step 4
.
Now we want to do a semi-join (a combined join and projection) with TEMP1
with TEMP2 (essentially joining smaller versions of S and SH), forming TEMP4
(snum, pnum) at 13 bytes per row, which has the same scheme as TEMP2. Note that
sorting of TEMP2 is not required here because TEMP1 is a very small table (much
less than one block). Even with 10% hit rates of records in TEMP2, virtually every
block will be accessed because of the very large blocking factor. Therefore, we don't
need an index in this situation.
Number of block accesses (step 4)
= read TEMP1 + read TEMP2 + write TEMP4
= 1 + 87 + 9
= 97.
Step 5
.
Now we have TEMP4 (snum, pnum) and TEMP3 (pnum, pname) that need to
be joined over pnum. To accomplish this with a minimum of I/O cost we do a semi-
join with TEMP4 projected over pnum before joining with TEMP3 and forming
TEMP5 (pnum, pname) with 18 bytes per row. No sorting of TEMP4 is required
because TEMP3 is very small (less than one block).
Number of block accesses (step 5)
= read TEMP4 + read TEMP3 + write TEMP5
= 9 + 1 + 13
= 23 a.
Step 6.
Finally we project TEMP5 over pname for the final result.
Number of rows = 10K (read TEMP5)
Number of block accesses (step 6)
