DB2 hash accesses is faster …why let us know about it…
Hash Access
A hash space is a defined disk space that organizes table data for hash access. When you enable hash access on a table, DB2® requires a defined amount of disk space to contain table data.
You can specify the amount of disk space to allocate to the hash space when you create a table or alter an existing table. The hash space on a table must be large enough to contain new rows that are added to the table. If a hash space is full, new rows are relocated to the overflow index, which reduces the performance of hash access on that table.
Hash spaces can contain only a single table in a universal table space, and can be partitioned by range or partitioned by growth.
In DB2 V10, we introduce a new basic access path that we call “Hash Access”. This is equivalent to “Index Scan for an equal predicate” in function, but faster. Note that when DB2 process an IN list, it also uses equal predicate processing.
How hash Access works
DB2 10 introduces a new method of accessing user data – Hash Access.
For an equal predicate query, 1 page will be accessed – the data page. Most likely the data page will be read from disk, but it may be possible that it is in the buffer pool.
- In comparison to the five level index access, the same query results in fewer page visits, reduced CPU and Elapsed time.
- The trade off is that hash access tables use slightly higher disk space
Srinimf 