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.