If we have an Index on rating:
- (1/NKeys(I)) * NTuples(R) = (1/10) * 40000 tuples retrieved.
- Clustered index: (1/NKeys(I)) * (NPages(I)+NPages(R)) = (1/10) * (50+500) pages are retrieved (= 55).
- Unclustered index: (1/NKeys(I)) * (NPages(I)+NTuples(R)) = (1/10) * (50+40000) pages are retrieved.
If we have an index on sid:
- Would have to retrieve all tuples/pages. With a clustered index, the cost is 50+500.
Doing a file scan: we retrieve all file pages (500).