An un- ordered student file has 20,000 records stored on a disk having the Block size as 1 K. Suppose that each student record is of 100 bytes, the secondary index field is of 8 bytes, and block pointer is also of 8 bytes, find how many block accesses on average may be saved on using secondary index on enrolment number.

Answer:

Number of accesses without using Secondary Index:

Number of records in the file = 20000

Block size = 1024 bytes

Record size = 100 bytes

Number of records per block = integer value of [1024 / 100] = 10

Number of disk blocks acquired by the file = [Number of records / records per block]

= [20000/10] = 2000

 

As the file is un-ordered any search on an average will require about half of the above blocks to be accessed. Thus, average number of block accesses = 1000

Number of accesses with Secondary Index:

Size of an index entry = 8+8 = 16 bytes

Number of index entries that can be stored per block

= integer value of [1024 / 16] = 64

Number of index entries = number of records = 20000

Number of index blocks = ceiling of [20000/ 64] = 320

Number of index block transfers to find the value in index blocks = ceiling of [log2320] = 9

One block move will be needed to get the data records using the index pointer after the needed index value has been located. So total number of block transmits with secondary index = 9 + 1 = 10

Therefore,, the Secondary index would save about 1990 block transmit for the given case. This is a huge saving compared to primary index. Please also compare the size of secondary index to primary index.

Let us now see an illustration of a secondary index that is on an attribute that is not an alternate key.

 

 

 

 

                               

Posted Date: 3/8/2013 6:09:28 AM | Location : United States

Ask an Expert

Related Discussions:- How many block accesses average saved on using secondryindex, Assignment Help, Ask Question on How many block accesses average saved on using secondryindex, Get Answer, Expert's Help, How many block accesses average saved on using secondryindex Discussions

Write discussion on How many block accesses average saved on using secondryindex
Your posts are moderated

Write your message here..

Related Questions

Develop network database ipad app, I want an iPad app that is a simple 1 pa... I want an iPad app that is a simple 1 page form to complete with text. The Objective is to allow a few dozen people of the same organization (they're all on iPad) to open the app,

Define single valued and multivalued attributes, Define single valued and m... Define single valued and multivalued attributes.  Single valued attributes: attributes with a single value for a particular entity are known as single valued attributes.   M

System Analysis and design, Using the questions found at the end of Chapter... Using the questions found at the end of Chapter 9 in your textbook, complete this homework activity. Students will submit a Word document answering questions 2, 3, and 4.

What are the ways of obtaining the connection parameters, What are the ways... What are the ways of obtaining the connection parameters?  Hardwire the parameters into the program. Ask for the parameters interactively. Get the parameters

Describe the functions and features of foreign key, Describe the functions ... Describe the functions and features of foreign key? Foreign Key : The primary key of one file or table that is implanted in another file or table to implement the relationships

Sorting field groups we cannot use more than one key field, While sorting f... While sorting field groups we cannot use more than one key field False.

Benefits of views-data control, Benefits: It limits data access. ... Benefits: It limits data access. It makes difficult queries look easy. It permits data independence. It presents dissimilar views of the similar data.

Define cascading rollback, Define Cascading rollback The phenomenon whe... Define Cascading rollback The phenomenon where a single transaction failure leads to a series of transaction rollbacks is known as Cascading rollback.

Draw enhanced er-diagram for hierarchy of a student database, Draw an EER d... Draw an EER diagram that captures the "is-a" hierarchy of students in another simple database. The database captures both the "basic" information (say, student ID and student name)

Primary key, The primary key is selected from the The primary key is selected from the