--- title: "DB2 Index Vs. Hash Scan: Top Differences" description: "Data access is much faster in Hashing compared to the index. Read the differences between Hashing and Index." url: https://srinimf.com/2018/11/01/db2-top-differences-read-on-index-vs-hashing/ date: 2018-11-01 modified: 2026-10-10 author: "Srini" image: https://i0.wp.com/srinimf.com/wp-content/uploads/2018/11/index-vs-hashing.jpg?fit=500%2C333&ssl=1 categories: ["DB2"] tags: ["DB2 Hashing Mechanism", "DB2 Tips and Solutions"] type: post lang: en --- # DB2 Index Vs. Hash Scan: Top Differences In DB2 or any other RDBMS the Insert, Delete, and Update are key 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. Related[The Essential Guide to 8 Index Access Paths in DB2](https://srinimf.com/2023/04/15/8-index-access-paths-db2-uses-for-sql-performance1-of-2/) ## 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 ](https://srinimf.com/2014/12/05/db2-hash-index-functionality/)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. #### Disadvantages 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](https://srinimf.com/2016/03/30/how-hash-access-is-faster-than-index-access-in-db2/) - 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](https://srinimf.com/2021/11/06/how-to-get-cpu-time-for-sql-query/) [enable Hash Access on a Table](https://www.ibm.com/support/knowledgecenter/en/SSEPEK_10.0.0/perf/src/tpc/db2z_hashaccesstype.html) read here. **Download Index vs Hashing Pdf** Related[DB2 for Beginners: What to Know Read Right Away](https://srinimf.com/2019/09/22/frequently-used-top-db2-words/) --- [**Index Vs Hashing Pdf**](https://srinimf.com/wp-content/uploads/2018/11/index-vs-hashing-pdf.pdf) --- ### 7 Types of [Indexes in DB2](https://srinimf.com/2022/01/17/how-to-create-dynamic-indexes-in-db2/) 1. Unique Index 2. Non-Unique Index 3. Bi-directional Index 4. Clustering Index 5. Non-Clustering Index 6. Partitioned index 7. non-partitioned index ## 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** - [Here’s Hash Index Real Purpose in DB2](https://srinimf.com/2014/12/05/db2-hash-index-functionality/)DateDecember 5, 2014 - [DB2 Access Path: What is New Hashing Technique](https://srinimf.com/2016/03/30/how-hash-access-is-faster-than-index-access-in-db2/)DateMarch 30, 2016 - [DB2 V10-New Access Method~Hashing (1 of 2)](https://srinimf.com/2013/04/12/db2-v10-new-access-method-hashing-1-of-2/)DateApril 12, 2013 ### Share this: - - [Tweet](https://twitter.com/share) - [![](https://i0.wp.com/assets.pinterest.com/images/pidgets/pinit_fg_en_rect_gray_20.png)](https://www.pinterest.com/pin/create/link/?url=https%3A%2F%2Fsrinimf.com%2F2018%2F11%2F01%2Fdb2-top-differences-read-on-index-vs-hashing%2F&media=https%3A%2F%2Fi0.wp.com%2Fsrinimf.com%2Fwp-content%2Fuploads%2F2018%2F11%2Findex-vs-hashing.jpg%3Ffit%3D500%252C333%26ssl%3D1&description=DB2%20Index%20Vs.%20Hash%20Scan%3A%20Top%20Differences) - - [Share on Tumblr](https://www.tumblr.com/share) - [Share on WhatsApp (Opens in new window) WhatsApp](https://srinimf.com/2018/11/01/db2-top-differences-read-on-index-vs-hashing/?share=jetpack-whatsapp) - [More](#) - - [Print (Opens in new window) Print](https://srinimf.com/2018/11/01/db2-top-differences-read-on-index-vs-hashing/?share=print) - [Share on Reddit (Opens in new window) Reddit](https://srinimf.com/2018/11/01/db2-top-differences-read-on-index-vs-hashing/?share=reddit) - [Share on Telegram (Opens in new window) Telegram](https://srinimf.com/2018/11/01/db2-top-differences-read-on-index-vs-hashing/?share=telegram) - [Email a link to a friend (Opens in new window) Email](mailto:?subject=%5BShared%20Post%5D%20DB2%20Index%20Vs.%20Hash%20Scan%3A%20Top%20Differences&body=https%3A%2F%2Fsrinimf.com%2F2018%2F11%2F01%2Fdb2-top-differences-read-on-index-vs-hashing%2F&share=email) - ### Like this: Like Loading…