Explain the concept of indexing in DBMS

DBMSDatabaseBig Data Analytics

Indexing is one of the techniques used to optimize performance of a database by reducing the number of disk accesses that are required when a query is processed.

A database index is a data structure that is helpful to quickly locate and access the data in a database table.

Indexes are created using database columns.

  • The first column is the Search key which contains a copy of the primary key or candidate key of the table.

  • The second column is the data reference that contains a set of pointers which hold the address of the disk block where the key value can be found.

Structure of Index

The structure of an index in the database management system (DBMS) is given below −

Search keyData reference

Types of indexes

The different types of index are as follows −

  • Primary
  • Clustering
  • Secondary

These types of indexes are listed below in the form of a chart −

Cluster Index

  • Index Entry will be created only for distinct values in a database.

  • This is both a dense and sparse type example.

Secondary Index

  • 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 level DB indexing is used.

Primary Index

  • Primary index is defined on an ordered data file. The data file is ordered on a key field. The key field is generally the primary key of the relation.

Published on 08-Jul-2021 07:30:03