What’s the Difference Between Index and Hash in DB2

In the DB2 database, Insert, Delete, and Update are real operations you do very often. To access your data, you need to use Index or Hash on the Table you wish to use. I am going to give you here the top differences between hash and index.

---

Index vs Hash scan (Read)

Index Advantages

1. To create Unique columns, you have to define an index.
2. When you are updating particular column more often, you had better avoid defining an index on that column.
3. You can define Index on multiple columns. The reason is to increase Uniqueness
4.  When the size of the table is less than 100 pages, you had better define one index.

Disadvantages

1. Adding more columns to index requires more storage. So it degrades performance.
2.  Always go for Index-only access. That means give Indexed columns in Where clause

Hashing Advantages

1. Hashing or Hash scan is faster than an index.
2. Hashing is used to insert or access data.
3. A hash value is generated during processing of Insert values. The same values will be used to retrieve the data.
4. You can define the Hash on the primary key column of the Table.
5. Hashing should be used on the Tables, where data is accessed more often using the Primary key.

Disadvanatages

1. Organizing Table data for hash access is a burden on the database.
2. When you define Hash space, the amount of storage you specfied is not enough, then the data will be relocated to another area. This causes performance degradation.

Hash Access Path

• You can use Hash access on Tables even-though indexes are defined.
• DB2 does not allow Hash access for the Tables defined with a Clustering index
• How to enable Hash Access on a Table read here.

Download Index vs Hashing Pdf

---

---

7 Types of Indexes in DB2

What is Hash Table

A Hash table in RDBMS contains addresses of all the Table data. During the Hash scan, it first looks in the Hash table to get the address.

Related Posts