Explain the concept of secondary index in DBMS

In secondary Index (Unique value) is created for each record in a data file which is a candidate key. Secondary index is a type of dense index and also called a non clustering index.

Secondary mapping size will be small as the two levels DB indexing is used.

• While creating the index, generally the index table is kept in the primary memory and the main table is kept in secondary memory because of its size.

• A table may contain thousands of records for this reason the sparse index becomes so large which cannot be handled in primary memory.

• Also, if we cannot keep the index in the primary memory then we lose the advantage of speed to access the data.

• For every large table, it is better to organize the index in multiple levels.

Example

Given below is an example of the secondary index −

Explanation

• Step 1 − Here the primary level index which is created 100 records with a gap and therefore smaller in size, is kept in the RAM for quick reference.

• Step 2 − If we need to find out the record of roll no 14, the index is first searched to find out the highest entry which is smaller than or equal to 14. We have 1,

• Step 3 − The adjoining pointer leads us to the anchor record of the corresponding secondary level index, where another similar search is conducted.

• Step 4 − This finally leads us to the actual data block whose anchor record is roll no 11.

• Step 5 − we now come to roll no 11 where a sequential search is made to find out roll 14.

Multilevel index is a modification of the secondary level index. In this system we may use even more numbers of levels in case the table is even larger.