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 term- Data Flows Data Flows: A data flow connects output of an object or process to input of another process or object. It represents an in-between data value wit

1 - Serial RS232 comm port comunication 2 - NanoHTTPD serving a few processed pages 3 - System tray application to indicate status and access configuration forms 4 - SQLit

real life situations where database management system are most suitable

Views A view is like a window by which data from tables can be changed or viewed. The table on which a view is based is known as Base table. The view is stored as a SELECT stat

Heap files (unordered file) Mostly these files are unordered files. It is the easiest and most basic type. These files having of randomly ordered records. The records will have

Sequences Sequences: Automatically produce unique numbers Are sharable Are typically used to make a primary key value Replace application code Speed up t

Define the following constraints for the table client. (i) BAL_DUE must be at least 1000. (ii) NAME is a unique key. (i)ALTER TABLE CLIENT ADD CONSTRAINT CLIENT_BAL_DUE_C1

(a)    Provide the description of the following ODMG schema in the Object Definition Language (ODL). (b)   Based on the above object database, write the following queries i

Explain weak and strong entity type ? Weak and Strong entity type: An entity set which does not have a primary key is referred to as a weak entity set. The existence of a wea

What are the several forms of identity? a) Value b) Name c) Built-in