HASH joins are the usual choice of the Oracle optimizer when the memory is set up to accommodate them. In a HASH join, Oracle accesses one table (usually the smaller of the joined results) and builds a hash table on the join key in memory. It then scans the other table in the join (usually the larger one) and probes the hash table for matches to it. Oracle uses a HASH join efficiently only if the parameter PGA_AGGREGATE_TARGET is set to a large enough value. If MEMORY_TARGET is used, the PGA_AGGREGATE_TARGET is included in the MEMORY_TARGET, but you may still want to set a minimum.
If you set the SGA_TARGET, you must set the PGA_AGGREGATE_TARGET as the SGA_TARGET does not include the PGA (unless you use MEMORY_TARGET as just described). The HASH join is similar to a NESTED LOOPS join in the sense that there is a nested loop that occurs—Oracle first builds a hash table to facilitate the operation and then loops through the hash table. When using an ORDERED hint, the first table in the FROM clause is the table used to build the hash table.
HASH joins can be effective when the lack of a useful index renders NESTED LOOPS joins inefficient. The HASH join might be faster than a SORT-MERGE join, in this case, because only one row source needs to be sorted, and it could possibly be faster than a NESTED LOOPS join because probing a hash table in memory can be faster than traversing a b-tree index.
As with SORT-MERGE joins and CLUSTER joins, HASH joins work only on equijoins. As with SORT-MERGE joins, HASH joins use memory resources and can drive up I/O in the temporary tablespace if the sort memory is not sufficient (which can cause this join method to be extremely slow).
Finally, HASH joins are available only when cost-based optimization is used (which should be 100 percent of the time for your application running on Oracle 11g).
Table 1 illustrates the method of executing the query shown in the listing that follows when a HASH join is used.
Table 1. HASH join
is there a mistake in the diagramm – the hash table is formed from small table which is dept and join key is use to probe the larger table employee.
me too thought same..any conclusion?
Its due to the ORDERED hint.
This helped. Thank you.
Hi,
One quick question.
When you build the hash table with join key as the “key”, what is the “value” in the hash table.
Hash tables consists of key-value pair. “Key” is join key here. What is the “value”?
Thanks,
Sanu
Hash joins use a hash function which basically converts the join column in the smaller table to a hash_value in the hash build table. It then probes the second (usually larger table) using the same hash function to return the full result set to the next step in the execution plan
Hi to all, the contents present at this web page are really remarkable for people experience, well, keep up the nice work fellows.
http://helpfultip.eu
Your URL http://helpfultip.eu does not work.
DNS_unresolved_error
Hi to every body, it’s my first go to see of this website; this webpage contains awesome and actually good stuff in support of readers.
Good one.. I liked the content. Please keep going.
Hi, can u pls explain y emp is considered as Hash Table whereas it is mentioned the smaller one usually be use as HT.
Hi Mohua,
Normally a hash join would choose the smaller table to hash. However in this query, there is an ‘ordered’ hint which instructs the optimizer to use the first table in the from clause – in this case, the first table is ’emp’