February 03, 2020
Amitraj
Indexing in DBMS
Indexing is used to optimize the performance of a database by minimizing the number of disk accesses required when a query is processed.
The index is a type of data structure. It is used to locate and access the data in a database table quickly.
OR
Indexing is defined as a data structure technique which allows you to quickly retrieve records from a database file. It is based on the same attributes on which the Indices has been done.
Index structure
Indexes can be created using some database columns.
-> 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. It contains a set of pointers holding the address of the disk block where the value of the particular key can be found.
Indexing Methods
Indexing is defined based on its indexing attributes. Indexing can be of the following types:-
1. Primary Index -
-> If the index is created on the basis of the primary key of the table, then it is known as primary indexing. These primary keys are unique to each record and contain 1:1 relation between the records.
-> As primary keys are stored in sorted order, the performance of the searching operation is quite efficient.
-> The primary index can be classified into two types: Dense index and Sparse index.
Dense Index
In dense index, there is an index record for every search key value in the database. This makes searching faster but requires more space to store index records itself. Index records contain search key value and a pointer to the actual record on the disk.
Sparse index
-> In the data file, index record appears only for a few items. Each item points to a block.
-> In this, instead of pointing to each record in the main table, the index points to the records in the main table in a gap.
2. Clustering Index
In a clustered index, records themselves are stored in the Index and not pointers. Sometimes the Index is created on non-primary key columns which might not be unique for each record. In such a situation, you can group two or more columns to get the unique values and create an index which is called clustered Index. This also helps you to identify the record faster.
Example:
Let's assume that a company recruited many employees in various departments. In this case, clustering indexing should be created for all employees who belong to the same dept.
It is considered in a single cluster, and index points point to the cluster as a whole. Here, Department _no is a non-unique key.
3. Secondary Index
The secondary Index can be generated by a field which has a unique value for each record, and it should be a candidate key. It is also known as a non-clustering index.
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; the mapping size always remains small.
Example of secondary Indexing
In a bank account database, data is stored sequentially by acc_no; you may want to find all accounts in of a specific branch of ABC bank.
Here, you can have a secondary index for every search-key. Index record is a record point to a bucket that contains pointers to all the records with their specific search-key value.
What is Multilevel Index?
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.
Advantages of Indexing
1. Offers Faster search and retrieval of data to users.
2. You can't sort data in the lead nodes as the value of the primary key classifies it.
Disadvantages of Indexing
1. You can't perform any other indexes on the Indexed data.
2. You are not allowed to partition an index-organized table.
3. SQL Indexing Decrease performance in INSERT, DELETE, and UPDATE query.
Indexing is used to optimize the performance of a database by minimizing the number of disk accesses required when a query is processed.
The index is a type of data structure. It is used to locate and access the data in a database table quickly.
OR
Indexing is defined as a data structure technique which allows you to quickly retrieve records from a database file. It is based on the same attributes on which the Indices has been done.
Index structure
Indexes can be created using some database columns.
-> 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. It contains a set of pointers holding the address of the disk block where the value of the particular key can be found.
Indexing Methods
Indexing is defined based on its indexing attributes. Indexing can be of the following types:-
1. Primary Index -
-> If the index is created on the basis of the primary key of the table, then it is known as primary indexing. These primary keys are unique to each record and contain 1:1 relation between the records.
-> As primary keys are stored in sorted order, the performance of the searching operation is quite efficient.
-> The primary index can be classified into two types: Dense index and Sparse index.
Dense Index
In dense index, there is an index record for every search key value in the database. This makes searching faster but requires more space to store index records itself. Index records contain search key value and a pointer to the actual record on the disk.
Sparse index
-> In the data file, index record appears only for a few items. Each item points to a block.
-> In this, instead of pointing to each record in the main table, the index points to the records in the main table in a gap.
2. Clustering Index
In a clustered index, records themselves are stored in the Index and not pointers. Sometimes the Index is created on non-primary key columns which might not be unique for each record. In such a situation, you can group two or more columns to get the unique values and create an index which is called clustered Index. This also helps you to identify the record faster.
Example:
Let's assume that a company recruited many employees in various departments. In this case, clustering indexing should be created for all employees who belong to the same dept.
It is considered in a single cluster, and index points point to the cluster as a whole. Here, Department _no is a non-unique key.
3. Secondary Index
The secondary Index can be generated by a field which has a unique value for each record, and it should be a candidate key. It is also known as a non-clustering index.
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; the mapping size always remains small.
Example of secondary Indexing
In a bank account database, data is stored sequentially by acc_no; you may want to find all accounts in of a specific branch of ABC bank.
Here, you can have a secondary index for every search-key. Index record is a record point to a bucket that contains pointers to all the records with their specific search-key value.
What is Multilevel Index?
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.
Advantages of Indexing
1. Offers Faster search and retrieval of data to users.
2. You can't sort data in the lead nodes as the value of the primary key classifies it.
Disadvantages of Indexing
1. You can't perform any other indexes on the Indexed data.
2. You are not allowed to partition an index-organized table.
3. SQL Indexing Decrease performance in INSERT, DELETE, and UPDATE query.
