DB2 top differences read on Index Vs Hashing

In 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 here the top differences between hash and index.

Index vs Hash scan

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

Hash scan

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 allowe Hash access for the Tables defined with Clustering index

Download Index vs Hashing Pdf

Types of Indexes in DB2 RDBMS

  • Unique Index
  • Non-Unique Index
  • Bi-directional Index
  • Clustering Index
  • Non-Clustering Index
  • Partitioned index
  • Non-partitioned index

What is Hash Table

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

Advertisements

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.

Start Discussion

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.