What is the Difference Between Indexing and Hashing

The main difference between indexing and hashing is that the indexing optimizes the performance of a database by reducing the number of disk accesses to process queries while hashing calculates the direct location of a data record on the disk without using index structure.

A database is a collection of associated data.  A DBMS or Database Management System allows creating, and managing data in the databases easily. The users can write SQL queries to perform operations on the tables of a database. DBMS allows multiple users to access and use data. Furthermore, it allows performing transactions and provides data protection. Indexing and Hashing are two concepts related to DBMS.

Key Areas Covered

1. What is Indexing
     – Definition, Functionality
2. What is Hashing
    – Definition, Functionality
3. What is the Difference Between Indexing and Hashing
     – Comparison of Key Differences

Key Terms

DBMS, Clustered Indexing, Hashing, Indexing, Ordered Indexing, Primary Indexing, Secondary Indexing, SQL

What is Indexing

When executing SQL queries, it takes some amount of time to access data from the disk. Herein, an index is a data structure that helps to find and access data in a table of a database quickly. Indexing technique reduces the number of disks accessed to process queries.

An index consists of two sections;  a search key and a data reference. The search key contains the primary key or the candidate key of the table. Data reference holds the address of the disk block that has the value corresponding to that key.

Also, there are various types of indexes. Some of them are as follows.

Ordered Indexing – Indices are sorted, making data searching faster

Primary Indexing – When the index is based on the primary key of the table, it is called a primary index. There are two types of indexes in primary key called dense and spare index. The dense index contains an index record for every search key value in the data file. In the spare index, there are index records for some data items.

Clustered indexing – Uses a combination of two or more columns to create an index. A group of records consists of records with the same characteristics. And, these groups create the indexes.

Secondary indexing – Contains another level of indexing to minimize the size of mapping.

What is Hashing

In a large database, it is not possible to search all the indexes to obtain the required data. Hashing helps to find the direct location of a specific data record on the disk without using indexing. Here, data blocks, also called data buckets, store data. A hashing function is a mathematical function. It helps to generate the addresses of those data blocks. Furthermore, the hashing function can select any column value to generate the address, but it usually uses the primary key to generate the address of the data block.

There are two types of hashing as static and dynamic hashing. In static hashing, the resultant data bucket address is always the same. However, static hashing causes bucket overflowing. Dynamic hashing is a solution to this issue. In dynamic hashing, data bucket increases or decreases depending on the number of records.

Difference Between Indexing and Hashing

Definition

Indexing is a data structure technique to efficiently retrieve records from the database files based on some attributes on which the indexing took place. On the other hand, hashing is an effective technique to calculate the direct location of a data record on the disk without using index structure. Thus, this is the main difference between indexing and hashing. 

Functionality

Indexing uses data reference that holds the address of the disk block with the value corresponding to the key while hashing uses mathematical functions called hash functions to calculate direct locations of data records on the disk. Hence, this is also a major difference between indexing and hashing.

Application

Another difference between indexing and hashing is that the hashing works well for large databases than indexing.

Conclusion

The main difference between indexing and hashing is that the indexing optimizes the performance of a database by reducing the number of disk accesses to process queries while hashing calculates the direct location of a data record on the disk without using index structure.

Reference:

1. “DBMS Indexing in DBMS – Javatpoint.” Www.javatpoint.com, Available here.
2. “DBMS Hashing – Javatpoint.” Www.javatpoint.com, Available here.

Image Courtesy:

1. “Hash table 4 1 1 0 0 1 0 LL” By Jorge Stolfi – Own work (Public Domain) via Commons Wikimedia