The HASH GROUP BY operation indexes and retrieves rows using hashing, and then organizes rows into groups.
The HASH GROUP BY PIVOT operation indexes and retrieves rows using hashing, and then organizes rows into groups in a pivot table.
Execution Plan image: or
HASH JOINis one of the algorithms that Oracle can use to join two tables.
In a HASH JOIN a hash table, an on-the-fly index, is constructed for the larger of the two tables. The smaller table is then scanned, and the hash table used to find matching rows in the larger table.
HASH JOIN joins tables by creating an in-memory bitmap of one of the tables and then using a hashing function to locate the join rows in the second table.
In the following query, the COMPANY and SALES are joined based on their common COMPANY_ID column.
from COMPANY, SALES
where COMPANY.Company_ID = SALES.Company_ID
and SALES.Period_ID =3
TABLE ACCESS FULL SALES
TABLE ACCESS FULL COMPANY
The Execution Plan shows that the SALES table is used as the first table in the hash join. SALES table will be read into memory. Oracle uses a hashing function to compare the values in COMPANY table to the records that have been read into memory.
When one of the tables is significantly smaller than the other in the join, and the smaller table fits into the available memory area, the optimizer will generally use a hash join instead of a traditional NESTED LOOPS join. Even if an index is available for the join, a hash join may be preferable to a NESTED LOOPS join.
The HASH JOIN BUFFERED operation joins buffered rows.