Re: Hash Join

eshevtsov_at_my-deja.com wrote:
>Hello.
>
>I have 2 questions about hash join.
>
>First. Does the server process (on behalf of my user process)
 copy
>table or index blocks of source to the db cache while as it's
 building
>the
>hash table from this source in the hash_area memory?
>
>Second. In which case the cardinality of result set of rows
 after hash
>join could be higher than the highest cardinality of its
 sources? Is it
>possible for equiajoin?
>
>Thanks in advance.
>
>Edward
>

A hash join operation still reads table blocks into the buffer pool. The row data is then hashed on the join columns and the data is written to the hash area. Next the second table data blocks are read into the buffer pool and the hash is again performed. If data exists in the hash area the new data is added to the hash area [however, since hashs can have collisions I am sure Oracle employs some form of key value verification]. The result set from a hash join, nested loops, or sort/merge on the same join conditions on the same data will be identical. If the hash area gets full while the result set is being built Oracle pages the hash blocks out to make more room. This increases the total IO necessary to support the operation and may make a nested loop a better choice.

I hope this helps.

Got questions? Get answers over the phone at Keen.com. Up to 100 minutes free!
http://www.keen.com Received on Mon Jul 24 2000 - 00:00:00 CDT