DBMS Indexing

Indexing is a way to optimize the performance of a database by minimizing the number of block of disk accessed required when a query is processed.
It is a data structure technique which is used to quickly locate and access the data in a database.

Index Structure

The first column of the database is the search key that contains a copy of the primary key or candidate key of the table. The values of the primary key are stored in sorted order so that the corresponding data can be accessed easily.
The second column of the database is the data reference / pointer. It contains a set of pointers holding the address of the disk block where the value of the particular key can be found.

If the index is created on the basis of the primary key of the table, then it is known as primary indexing.
The dense index contains an index record for every search key value in the data file. It makes searching faster.
In the data file, index record appears only for a few items. Each item points to a block.

Sometimes the index is created on non-primary key columns which may not be unique for each record.
In this case, to identify the record faster, we will group two or more columns to get the unique value and create index out of them.
This method is called a clustering index.

The secondary index can be generated by a field which has a unique value for each record.
This two-level database indexing technique is used to reduce the mapping size of the first level. For the first level, a large range of numbers is selected, because of this mapping size always remains small.

Multilevel Indexing is created when a primary index does not fit in memory.
In this type of indexing method, you can reduce the number of disk accesses to short any record and kept on a disk as a sequential file and create a sparse base on that file.

B-tree index is the widely used data structures for indexing.
It is a multilevel index format technique which is balanced binary search trees.
All leaf nodes of the B tree signify actual data pointers.
In B-Tree indexing, all leaf nodes are interlinked with a link list, which allows a it to support both random and sequential access.
Properties:
Every path from the root to leaf are mostly of equal length.
Every node which is not a root or a leaf has between n/2 and n children, where n is the degree of B-tree.

It helps you to reduce the total number of I/O operations needed to retrieve the data, so you don’t need to access a row in the database from an index structure.
Offers faster search and retrieval of data to users.
Indexing also helps you to reduce table space as you don’t need to link to a row in a table, as there is no need to store the ROWID in the Index.
Thus you will able to reduce the table space. You don’t need to sort data in the lead nodes as the value of the primary key classifies it.

To perform the indexing database management system, you need a primary key on the table with a unique value.
You are not allowed to partition an index-organized table.
SQL indexing decrease performance in INSERT, DELETE, and UPDATE query.