RE: Cost of hash join

Hi,
Part of the problem must be related to the size of the table being = hashed, I remember reading that if it cannot all sit in memory that = Oracle creates many hash maps that get paged in and out of memory.

Regards
Pete

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Christian Antognini Sent: 07 June 2005 15:09
To: new_dba_on_the_block_at_yahoo.com
Cc: ORACLE-L_at_freelists.org
Subject: RE: Cost of hash join

Hi Naveen
=20

>cost=3D (access cost of A * number of hash partitions of

>B) + access cost of B.

>=20

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

>partitions?

=20

In my opinion this is not a good one (perhaps for this reason it's no = more in the 10g documentation...).

In fact the cost is something like:

=20

cost =3D access cost A + access cost B + cost join.

=20

If the hash table is "small", the cost of the join itself will be close = to 0.

If the hash table is "large", the cost is strongly dependent on the = amount of "available" PGA memory.=20

=20

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

=20

Each table is read only once.

=20

=20

HTH Chris

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