First page Back Continue Last page Graphics
Hash Joins…
Good technique for large sets
- When sufficient PGA available
Became available with Oracle 7.3
- CBO only
- RULE optimizer will never use hash join
Pre-processing step delays return of 1st row
Really fast once rows start returning
Notes:
The hash join, introduced with Oracle 7.3, is becoming a very common join technique, especially on servers with lots of PGA memory dedicated to Oracle. This technique can only be used by the cost-based optimizer; using a RULE hint cause Oracle to choose a nested loops or sort merge join.
Unlike the nested loops join, the hash join has a pre-processing step (see details on next page) that delays the return of the 1st row. But once it starts returning rows, this is a very fast join technique.