How many block accesses average saved on using secondryindex, Database Management System

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.

 

 

 

 

                                269_how many block accesses average saved on using Secondryindex.png

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







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
Related Questions
1. Explain different addressing modes of 8086 Explanation of all addressing modes 2. Write a sequence of instructions to exchange two register contents using stack

Give an example of enforcement in Typing. You can understand the idea of enforcement as it makes sure objects of dissimilar classes may not be interchanged as below:  Exampl

10A.53 Code an Oracle Database trigger to enforce the constraint that an employee can never change his or her department. 10A.54 Code an Oracle Database trigger to allow the

What is Object Oriented model? This model is based on collection of objects. An object has values stored in instance variables with in the object. An object also having bodies

What is serializability? Explain its types? The data base system must control concurrent implementation of transactions, to ensure that the data base state remains consistent.

Support by Adding Indexes Multiple indexes can be used to access a data file by multiple access paths. In such a scheme only single copy of the data is consider, only the numbe

each publisher has uniqe name a mial addrs are also kept on each pubisher.a publisher publish one book.a book publish y one publisher.book identified by isbn.each book writn by one

What is theta join explain? Theta  Join  - The  theta  join  operation  is  an  extension  to  the  natural-join operation which permits us to merge selection and a Cartesian

Concatenation operator Example : Printing name and job as one string as column name employees: SELECT ENAME||JOB "EMPLOYEES" FROM EMP;

1) Define a job scheduling strategy that will meet business requirement of reporting availability by 6am CST for the following cubes? Show the job scheduling dependencies in a pict