"single partition hash join" vs "merge join"

[balaluck]

Hello,

What is the difference between "single partition hash join" and "merge join" ? Is there any other difference other than "single partition hash join"

1. avoids sorting data.
2. requires right table to be redistributed in all amps ?

I have seen a "sql" which performs much better in "merge join" (in prod env). But that same "sql" is using "single partition hash join" in dev env with poor performance.

How can we fix this ?

Thanks

 

[dnoeth]

What about statistics? Maybe they're missing on the dev system.

You can't force the optimizer to use a certain join type.
But you can switch off hash joins using dbscontrol:
performance fields HTMemAlloc

For more info look at SQL Manual Vol.2 -> Query Optimization

Dieter