DIff b/w Hash join and Product join

Database
Enthusiast

DIff b/w Hash join and Product join

Can anyone explian me difference  between Hash join and Product join.Based on what constraints and conditions the optimiser will select them.

Please elaborate......

Tags (1)
2 REPLIES
Enthusiast

Re: DIff b/w Hash join and Product join

Hi,

Product join : It is a cross product of two tables (nothing but a cross join). Generally ,it occurs 

                   Using   - cross join, multiple inequalities, multiple OR conditions

Hash Join :  Hash join is a part of Merge join. Hash join uses memory or cache for one table & join its by hash to an unsorted spool

                  Main table in cache

                  Other table join without sorting

Comparetively Hash Join is faster than Product Join

Please correct me, if my statement is wrong!! Thanks in adavance

Enthusiast

Re: DIff b/w Hash join and Product join

Basing on situation, Optimizer select the join.. If it is small table product join is ok comparitvely than hash join.. So we need to take explian plan for both produc join and hash join .. Watch the  how much spool it takes and estimate time secs