Lecture 14
Homework solutions
S: NPages(S) = 500, NTuplesPerPage(S)= 80
Copyright By PowCoder代写 加微信 powcoder
R: NPages(R) = 1000, NTuplesPerPage(R) = 100 B: NPages(B) = 10, NTuplesPerPage(B) = 10 SMJ : 2 passes, RxB: 10 tuples per page
SELECT S.sname, B.bname, R.day FROM Sailors S, Reserves R, Boats B WHERE S.sid = R.sid AND R.bid = B.bid
(heap scan) (heap scan)
(heap scan)
Cost(RxB) = Sort(R) + Sort(B) + Read(R) + Read(B) = 2*2*NPages(R) + 2*2*NPages(B) + NPages(R)+ NPages(B) = 5*(1000 + 10) = 5,050
Result size (RxB) = 100,000*100 * 1/100 = 100,000 tuples = 10,000 pages – note that join here is on R.BID = B.BID and because BID is the primary key of B, NKeys(BID) = 100, also be aware that NKeys is the number of DISTINCT keys Cost (xS) = 10,000*500 = 5,000,000 (first read of the left input to NLJ has been done due to pipelining)
Total cost = 5,050 + 5,000,000 = 5,005,050
S: NPages(S) = 500, NTuplesPerPage(S)= 80
R: NPages(R) = 1000, NTuplesPerPage(R) = 100 B: NPages(B) = 10, NTuplesPerPage(B) = 10 SMJ : 2 passes, RxB: 10 tuples per page
I(S.sid); NPages(I) = 50
SELECT S.sname, B.bname, R.day FROM Sailors S, Reserves R, Boats B WHERE S.sid = R.sid AND R.bid = B.bid
The second SMJ is a tricky one. The right input (S) is sorted
so we don’t need to pay the cost of sorting, we can read it
through the index on S, however we NEED to sort the left input, because the previous SMJ sorted it by BID, and the second
SMJ requires the data to be sorted by SID (since this is the join condition). But the first read to the left input is already done due to pipelining so we need to subtract it!
(INDEX scan)
(heap scan) (heap scan)
Cost(RxB) = Sort(R) + Sort(B) + Read(R) + Read(B) = 2*2*NPages(R) + 2*2*NPages(B) + NPages(R)+ NPages(B) = 5*(1000 + 10) = 5,050
Result size (RxB) = 100,000*100 * 1/100 = 100,000 tuples = 10,000 pages – note that join here is on R.BID = B.BID and because BID is the primary key of B, NKeys(BID) = 100, also be aware that NKeys is the number of DISTINCT keys Cost (xS) = Sort(RxB) + Read(S through index) + Read (RxB) = 2*2*NPages(RxB) + NPages(I(sid)) + NPages(S)
= 4* 10,000 + 50 + 500 = 40,550
Sort is 2*2*Npages(RxB) plus Read(RxB) for the join – Read(RxB) due to pipelining gives us 4*Npages (RxB)
Total cost = 5,050 + 40,550 = 45,600
S: NPages(S) = 500, NTuplesPerPage(S)= 80
R: NPages(R) = 1000, NTuplesPerPage(R) = 100 B: NPages(B) = 10, NTuplesPerPage(B) = 10 SMJ : 2 passes, RxB: 10 tuples per page
SELECT S.sname, B.bname, R.day FROM Sailors S, Reserves R, Boats B WHERE S.sid = R.sid AND R.bid = B.bid
(heap scan) (heap scan)
In this case we need to sort both inputs to the second SMJ.
The left input is sorted by BID, and we need it now by SID, and
the right input is not sorted at all. First read of the left input of the Second SMJ is already done due to pipelining.
(heap scan)
Cost(RxB) = Sort(R) + Sort(B) + Read(R) + Read(B) = 2*2*NPages(R) + 2*2*NPages(B) + NPages(R)+ NPages(B) = 5*(1000 + 10) = 5,050
Result size (RxB) = 100,000*100 * 1/100 = 100,000 tuples = 10,000 pages – note that join here is on R.BID = B.BID and because BID is the primary key of B, NKeys(BID) = 100, also be aware that NKeys is the number of DISTINCT keys Cost (xS) = Sort(RxB) + Sort(S) + Read (RxB) + Read (S)= 2*2*NPages(RxB) + 2*2*NPages(S) + NPages(S)
= 4* 10,000 +5*500 = 42,500
Total cost = 5,050 + 42,500 = 47,550
One Read(RxB) is deducted due to pipelining
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com