What is Indexing in DBMS?

What is Indexing in DBMS?

Indexing is a data structure method to efficiently retrieve records from the database files based on some attributes on which the indexing has been made. 

Indexing can be of the following kinds:

1. Primary Index: Primary index data file is ordered on a key field. The key field is generally the primary key of the relation.

2. Secondary Index: Secondary index is produced from a field which is a candidate key and has a unique value in every record or a non-key with copy values.

3. Clustering Index: The data file is ordered on a non-key field, so that Clustering index is characterized on an ordered data file. 

What is Dense Ordered Index?

In Dense Ordered Index, there is an index record for every search key value in the database making searching more rapid but needs enough space to save index records itself. 

What is Sparse Ordered Index?

During sparse index, index records are not produced for every search key. An index record here has a search key and an a pointer to the data on the disk. To start a search, we first progress by index record and reach at the actual location of the data. If the data we are searching for is missing here, the system starts sequential or chronological search until the data or file in question is found.

What is Multilevel Index?

In Multilevel indexing, there is an immense need to keep the index records in the main memory so as to speed up the search operations. Multi-level Indexing breaks down the index into numerous smaller indices in order to make the outermost level so small that it can be stored in a single disk block, which can easily be accommodated anywhere in the main memory.