First page Back Continue Last page Graphics
…Hash Joins
Use when intend to return the entire set
Only available for equi-joins
Why CBO might not select hash join?
- Join condition is not equality
- Shortage of memory
- See PGA_AGGREGATE_TARGET or HASH_AREA_SIZE parameter
- Hash joins disabled
- See HASH_JOIN_ENABLED parameter
Notes:
Since a hash join can spend considerable time creating a hash table, this join technique is typically useful when you intend to read the entire result set. If your intention is to read just the first row or first 100 rows, a nested loops join is probably a better choice.
Reasons hash join might not be available or selected include:
Shortage of memory. Storing the hashed table entirely in PGA memory lowers the cost of the hash join. However, if memory is in short supply and the hash table needs to be written to disk (it uses temporary tablespace), the costs goes up. This might cause CBO to select a nested loops or sort merge join.
An equi-join is not coded. Since the hash algorithm hashes the join key to the same value, and that value is used to search the hashed table, range joins are not supported.
Hash joins have been disabled. Oracle offers the HASH_JOIN_ENABLE parameter which can be set at the instance or session level to disable hash joins.