Hash clusters in IBM DB2

Does IBM DB2 support the ability to store and retrieve rows according to a hash function (a.k.a. "hash clusters")?

Generally, databases locate rows using key values that the database stores in a separate index. Using the "hash clusters" feature, a database can physically store the rows of a table in a hash cluster and retrieve these rows according to a hash function. Very cool :)

Thank you.
cyber-33Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

momi_sabagCommented:

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
wolfgang_93Commented:
Are you asking about hash joins? The IBM database product DB2 has such a feature available
and is explained here in this PDF:

http://www.cs.uci.edu/bin/pdf/seminarseries2k9/Lindsay.pdf
cyber-33Author Commented:
momi_sabag  and wolfgang_93 - thank you for your suggestions.

It looks like key sequence table and range-clustered tables do NOT involve hashing (although the idea seems to be very similar). Here is what IBM says on point:

http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.admin.doc/doc/c0011068.htm

Hash Joins also do not allow to store and retrieve records accroding to a hash function - instead it seems to be an efficient method of performing join operations on the matching columns.

wolfgang_93Commented:
Ok, getting back to your original question...

DB2 does have an option whereby you can have a clustered index, which I believe
answers 1/2 of your question as I do not believe it uses a hash method for doing
searches on it. See:
http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?topic=/com.ibm.db2.luw.admin.dbobj.doc/doc/c0020180.html

My understanding in DB2 is that the method of searching of any index is that the indexes is
built up as a structure called a balanced B-tree which allows for very efficient and optimal
searching. Though a hash index seems cool and fast, it does not approach the efficiency
of a balanced B-tree index.
cyber-33Author Commented:
Wolfgang 93,

The reference that you provided describes clustered indexes. The indexes store only record IDs and not the entire records. To retreive information, the database has to: (1) locate the id in the index and (2) retrieve the values according to the id. This is exactly what hash clusters are designed to avoid.

As explained in my question above, the data retrieval of hash indexes can be performed in a single step, because the entire record (as opposed to just ids) is stored within the cluster.

momi_sabag had correctly identified the concept. Unfortunately his solution did not use hashing, but his answer was the closest.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.