A4sol - University of Waterloo School of Computer Science...

Info iconThis preview shows pages 1–2. Sign up to view the full content.

View Full Document Right Arrow Icon
University of Waterloo School of Computer Science CS338 Winter 2009 Assignment 4 Sample Solution Question 1: Index a) Explain why it is not possible to have more than one clustered index per relation. Answer: Data can only be physically stored and organized in one way. If the data is clustered in by one attribute, then it is not possible to cluster it by another attribute. b) Consider Relation R [a,b], where a and b are attributes and Attribute a is the primary key R has 1,000,000 records Record size is 100 bytes Disk block is 4K bytes Index nodes hold 200 entries For each of the following scenarios, give the maximum number of disk I/Os 1) search for a=10 using a clustered index on a 2) search for a=10 using non-clustered index on a 3) search for b>10 using non-clustered index on b Answer: 1) Assuming each block is 100% full Total number of records in each block: 4096/100=40.96, hence 40 records Total blocks needed for R: 1,000,000/40 = 25,000 Each index holds 200 entries; hence the index tree has three levels (2 I/Os needed since root is always in memory) in the case of non-clustered index and two levels (one I/O needed) in the case of clustered index (see slides for illustration) For a=10 using clustered index, since a is primary key, there is only one record with a=10. Hence, the total number of blocks visited is
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Image of page 2
This is the end of the preview. Sign up to access the rest of the document.

This note was uploaded on 05/28/2011 for the course CS 338 taught by Professor I.i during the Winter '09 term at Waterloo.

Page1 / 4

A4sol - University of Waterloo School of Computer Science...

This preview shows document pages 1 - 2. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online