This preview shows pages 1–3. Sign up to view the full content.
Problem 1: Query Execution
Consider the following two relations:
Students (sid, name, latestcourseid)
TakeCourses (sid, cid, cname)
where sid, cid, and cname are student id, course id, and course name, respectively.
Students has 10,000 tuples, with 25 tuples fitting on a block. TakeCourses has 5,000 tuples,
with 50 tuples fitting on a block. There is no index on any attribute of the relations. You may
assume the tables are "clustered".
(1) Suppose the memory buffer has 101 blocks. Compute the cost of using a blocknested loop
join to join the above two relations.
(2) Suppose we wanted to join the two relations using a blocknested loop join and limit the cost
to 900. What is the smallest value M can be?
(3) What is the cost of joining R1 and R2 using a hashbased join?
Solution
(1)
B(S) = 10,000 / 25 = 400
B(T) = 5,000 / 50 = 100
Cost = B(T) + B(S) x ( B(T) / (M1) ) = 100 + 400 x ( 100/(1011) ) = 500
(2)
B(S) = 10,000 / 25 = 400
B(T) = 5,000 / 50 = 100
B(T) + B(S) x ( B(T) / (M1) )
≤
900
100 + 400*( 100/(M1) )
≤
900
100/(M1)
≤
2
50
≤
M 1
51
≤
M
So M must be at least 51 blocks.
(3) 3B(R) + 3B(S) = 1,500
This preview has intentionally blurred sections. Sign up to view the full version.
View Full DocumentProblem 2: Cost Estimation
Consider a database with three relations, R1(A,B,C), R2(B,D), R3(C,E,F), with statistics:
•
T(R1)=100
•
V(R1,A)=100
•
V(R1,B)=20
•
V(R1,C)=50
•
T(R2)=2,000
•
V(R2,B)=50
•
V(R2,D)=100
•
T(R3)=1,000
•
V(R3,C)=100
•
V(R3,E)=200
•
V(R3,F)=250
Assume that the data in every relation for each field is the set of integer values from 1 to the
number of individual values specified for each field, evenly distributed. For instance, V(R1,B) =
20 implies that the B field in relation R1 has an even distribution of the integer values 1 to 20.
For each query, estimate the number of tuples returned and briefly show your work:
This is the end of the preview. Sign up
to
access the rest of the document.
 Spring '07
 Winslett

Click to edit the document details