consider the following information regarding the two relationships R and S Quest

Question

consider the following information regarding the two relationships R and S

Question (50 points) 1. (10 polnts) Consider the following information regarding two relations R and s. . Relation R contains 4,000 tuples with blocking factor 40. Relation S contains 2,000 tuples with blocking factor 40. . Attribute a of relation R refers to attribute a of relation S Attribute a of relation S is the primary key for S Both relations are stored as simple heap files and 27 buffer caches (pages) are available. Each tuple in S matches with two tuples in R on average a) (5 pts) Compute I/O costs for the following join operations using a block nested loop join. b) (5 pts) Assume thet the attribute R.a has the secondary Index with index level 2 and the attribute S.a has the secondary index with index level 1 Compute l/O costs for the following join operations using a Index based nested loop join.

Explanation / Answer

The block nested loop join costing (I/O) is calculated as below
I/O= Outer page+ (outer page/block size)*innerpage
A.a
I/O= 100(tupples/blocking factor)+ (100/40)*50
I/O=100+125
I/O (with R as outer relation)=225

I/O= 50(tupples/blocking factor)+(50/40)*100
I/O=50+125
I/O (with S as outer relation)=175.

A.b The index nested loop cost metric is calculated as below
I/O=Outer page+(Outer page*Outerpage/block size)*cost of matching inner tupples

I/O= 100+(100/40)*1*50
I/O (with R as outer relation)=100+125
225

I/O= 50+(50/40)*2*100
I/O(with S as the outer relation)= 50+250
300.

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.