First page Back Continue Last page Graphics
Hash Join Process…
Use driving table to create a “hash table”
- Join key value hashed – pointer into table
- Hash value and row stored in private memory
- Uses Temp space if too big
Read rows from second table
- Hash join key value
- Probes the hash table to get match
Return joined row if match found
Notes:
The hash join process involves the following steps:
Read the driving table (CBO will designate the smaller table as the driving table) and for each row read, hash the join key value. The hash value is used as an index into the hash table (see next step).
Create a private memory-based table (array). Each entry in the array contains the hashed value and the row from the driving table. If this array is too large for the private memory available to the session, Oracle will use temp disk space. This will raise the cost of the hash join and lower the chances (but certainly not eliminate) that CBO will choose this technique. This pre-processing step causes an initial delay; i.e. the client will not start receiving rows as fast as a nested loops join would produce them. However, the cost and elapsed time to return the entire result set is often far less than the nested loops.
Notes for this slide continue on the next page…