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
Develop a database broker framework to implement a persistence mechanism for the domain objects involved in the use case dealt with in Question 2.  a. Specify the attribute type

What is inheritance? Define it in detail?  Inheritance Inheritance can be at the levels of types, or at the level of tables we first consider inheritance of types, then i

Define What are the desirable properties of a decomposition Ans: What are the desirable properties of decomposition - dependency preserving.  Because Lossless join and dependen

Explain the Concept of Two-phase Locking It concerns the positions of locking and unlocking operations in each transaction.  In this protocol every transaction issue lock and u

Explain the paths in the association network We can analyze use of paths in the association network as given below: Calculate each operation Find associations whic

What is relational model feature? A relational model feature is much more data independence than some other database models.

What is DML (Data Manipulation Language)? This language that enable user to access or manipulate data as organised by appropriate data model. Procedural DML or Low level: DM

Explain the function of input controls. Identify four types of input control and explain the function of each. Provide an example of a data integrity error that could occur i

Explain the use of constraints in functional model with suitable example. A constraint shows relationship between two objects at same time, or   among various values of the sam

Define about the Stored Procedure - It's a set of T-SQL statements combined together to perform a single task formed by combining numerous small tasks. - When you essentiall