DBMS Menu

Primary Indexes in DBMS



A primary index is an ordered file whose records are of fixed length with two fields. The first field is the same as the primary key of the data file, and the second field is a pointer to the data block where that specific key can be found.

The primary index can be classified into two types

  1. Dense Index: In this, an index entry appears for every search key value in the data file.
  2. Sparse (or Non-Dense) Index: Here, index records are created only for some of the search key values. A sparse index reduces the size of the index file.

Characteristics of a Primary Index:

  1. It is based on the primary key of the table.
  2. The primary key must have unique values.
  3. The index entries are sorted in the same order as the data file (hence often a clustered index).
  4. The index has one entry for each block in a data file, not for each record.

Primary Index Example

Let's assume a simple table named `Students` that stores data about students. The table has the following structure:


| RollNumber (Primary Key) | Name  | Age |
|--------------------------|-------|-----|
| 1001                     | Madhu | 20  |
| 1003                     | Mahi  | 22  |
| 1007                     | Ramu  | 21  |
| 1010                     | Durga | 23  |

Assuming each block of our storage can hold two records, our blocks will be:

  • Block 1: Contains records for RollNumbers 1001 and 1003.
  • Block 2: Contains records for RollNumbers 1007 and 1010.

Dense Primary Index:


| Key (RollNumber) | Pointer to Block |
|------------------|------------------|
| 1001             | Block 1          |
| 1003             | Block 1          |
| 1007             | Block 2          |
| 1010             | Block 2          |



Sparse Primary Index:


| Key (RollNumber) | Pointer to Block |
|------------------|------------------|
| 1001             | Block 1          |
| 1007             | Block 2          |



In the dense primary index, there's an entry for every key value, whereas in the sparse primary index, only the first key value of each block (the block's anchor record) gets an entry in the index. In this way, sparse indexes are more compact but might require more sequential block reads if a search key isn't an anchor for a block.

Benefits of a Primary Index:

  • Fast retrieval of records based on the primary key.
  • Efficient for range-based queries due to sorted order.
  • Can be used to enforce the uniqueness of the primary key.

However, while the primary index provides rapid access and ensures the uniqueness of records, it can be slower for insertions, updates, and deletions, as maintaining the sorted order of the index can be time-consuming.

Next Topic :Secondary Indexes in DBMS