Re: Simple question: What is a hash join?

Ok, I'll take a shot at this. Generally, hash joins are used in an SQL join between 2 or more tables. Most often it will occur when the tables involved do not have an index on the join columns(s). Basically a hash join involves the creation of a hash table for the smaller of the two tables (smaller number of rows). A hash table is a data structure where each item runs through an algorithm which classifies the row into hash buckets (usually the number of buckets is configurable). The goal of the hash algorithm is to evenly distribute all rows across the given number of hash buckets. The join column data is used by the hash algorithm. And if multiple rows fall into the same hash bucket the data structure resolves with a linear list for those buckets. Once the hash table is built the other table is read sequentially and a hash lookup is done on the table which had the hash table built for it. Now remember that this is used most ofetn when indexes are absent because the engine must either read all tables in the join sequentially or build indexes on the fly in this case. -Bill > > Hi. > > I've been hearing about hash joins for quite a while now and I've > always been kind of foggy on exactly what they are. As I understand > it, the optimiser sometimes decides to do a hash join for a join > between two tables, but I don't understand why. I'm guessing that if > I knew what a hash join was, everything would be a lot clearer. > > Thanks, > > Steve Kain > Peapod Inc. > > > -- Bill Ennis Voice: 312-474-7516 SSA Fax: 312-474-7460 500 W. Madison email: ennis@ssax.com ennis@accesschicago.net