Hash join Mechanics
Hi All,
I would apprciate, if someone make me understand how Hash Join work under the cover. I also gone through Mr. Kytes "Effective Oracle By Design". but not able to understand fully. Also, if you go through with following sql.
select t1.object_name, t2.object_name
from t t1, t t2
where t1.object_id = t2.object_id
and t1.owner = 'WMSYS'
According to Mr Kyte, The join key—OBJECT_ID, in this case—was hashed to an
index in the hash table.
what actually, he trying to convey in above lines ? Also, when we the optimizer
chosses hash join, does it always create a hash table in our local memory, hence
in PGA right? SO, it does not need to latch them in order to inspect them.
Can you also brief the above said ?
hare krishna
Alok