Cost of hash join

From: Naveen <new_dba_on_the_block_at_yahoo.com>
Date: Tue, 7 Jun 2005 06:22:38 -0700 (PDT)
Message-ID: <20050607132238.68326.qmail@web53309.mail.yahoo.com>

Hi All,

While going through the Oracle performance tuning guide, I undestood the rationale behind the cost of Sort-merge and nested loops joins.

But I failed to understand the cost associated to a hash join. The guide says that the cost of hash join is:

cost= (access cost of A * number of hash partitions of B) + access cost of B.

Why is the access cost of A multiplied by no. of hash partitions?

Is "A" read for each hash partition of B?

What I understand from the description of the hash join in the tuning guide or from a few articles in metalink is that Oracle reads "A", hashes the join key and matches it with the corresponding hash partition of "B" which is in memory.

So theoretically "A" should only be read once for all the partitions in the memory.

Please let me know if I misread/misunderstood something.

Naveen

---

Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Jun 07 2005 - 09:27:38 CDT