Re: HASH-JOIN

Bard wrote:
>
> I occasionally see "HASH JOIN" in explain plans, but my brain hasn't yet
> comprehended what's occuring. I'm sure I'm likely being dense. I
> understand hash clusters (though I'm not using any), but I'm lost when
> Oracle says it's doing a HASH JOIN. I make extensive use of explain plans
> to assist ad hoc users with performance, and I'd really like to understand
> what's happening here. Could anyone please help?

To perform a hash join, Oracle follows these steps:

1. Oracle performs a full table scan on each of the tables and splits each into as many partitions as possible based on the available memory.
2. Oracle builds a hash table from one of the partitions (if possible, Oracle will select a partition that fits into available memory). Oracle then uses the corresponding partition in the other table to probe the hash table. All partitions pairs that do not fit into memory are placed onto disk.
3. For each pair of partitions (one from each table), Oracle uses the smaller one to build a hash table and the larger one to probe the hash table.

---
Name   : Lun Wing San
Title  : Oracle Application Developer of Hong Kong Productivity Council
         Oracle Database Administrator and System Administrator of QRC
Phone  : (852)27885841