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

Example 1: An ordered student file (ordering field is enrolment number) has 20,000 records stored on a disk having the Block size as 1 K. Assume that each student record is of 100 bytes, the ordering 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 primary index.

Answer:

Number of accesses without using Primary 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

Suppose a block level binary search, it would require log22000 = about11 block accesses.

Number of accesses with Primary 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 disk blocks = 2000

Number of index blocks = ceiling of [2000/ 64] = 32

Number of index block transfers to find the value in index blocks = log232 = 5

One block transfer 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 primary index = 5 + 1 = 6.

Therefore, the Primary index would save about 5 block transmits for the given case.

Are there any drawbacks of using primary index? Yes, a primary index needs the data file to be ordered, this causes troubles during deletion and insertion of records in the file. This difficulty can be taken care of by selecting a suitable file organisation that permits logical ordering only. 

Posted Date: 3/7/2013 6:25:58 AM | Location : United States







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

Write discussion on How many block accesses average saved on using primary index
Your posts are moderated
Related Questions
Explain the integrity constraints: Not Null,  Unique, Primary Key with an example each. Is the combination 'Not Null, Primary Key' a valid combination. Justify. Not Null - Shou

The physical location of a record is determined by which mathematical formula that transforms a file key into a record location? The physical location of a record is determined

Determine the method of Choosing Algorithms In general, most of operations are easy and have a satisfactory algorithm since description of what is to be done also shows how i

Can you scan double-sided documents - and remove blank pages? Our software and imaging systems supports duplex scanning, at the same time scanning both sides of a page. Program

How many types of users works on database? Users are differentiated through the way they expect to interact along with the system (i) Application programmers-interact with

Describe the nested-loop join and block-nested loop join.   Ans:   The block nested- loop join algorithm is as described below:     for every block B r of relation R do beg

Define database management system? Database management system (DBMS) is a set of interrelated data and a combination of programs to access those data.

I need web security expert , website management Project Description: i want professional for the below . website management server management database management

Explain the term- Query. Queries are used to repossess and show specific records in a table. They can also be used to show specific fields of specific records, which meet the co

What are ways of finding out either an update is required or not for derived attributes? The ways to find out an update is required or, not are as: Explicit update