Hash Joins

Database
Enthusiast

Hash Joins

Are there any downsides of using Hash Joins. Apart from disabling the Hashjoins thru dbscontrol, is there any other way that a user can dictate teradata to not to choose Hash joins for their queries?

Can Hash joins perform better than a merge join or is it cost based?

1 REPLY
Enthusiast

Re: Hash Joins

Partial reply to your questions and would be looking for other's input.

If the join column(s) are PI of both the tables, a merge join will perform best. ( No data redistribution or data sorting is needed)

If the join columns are PI of one of the tables or neither of the tables, a hash join would be better than a merge join for the fact that it doesnot sort the larger table.

However, parser can choose hash join only when the qualifying rows of small table can be held in AMP memory.