Merge Join Not happening with more than 2 tables
I have 3 tables A,B & C. The query is select * from A inner join B on (A.property_1=B.Property_1) inner join C on (A.property_2=C.property_1)
I have Projection defined for A,B,C sorted by join condition. For A sort is defined as sorted by property_1,property_2.
If I join only A&B or A&C, the explain plan shows merge join. But If all 3 are joined only one of the join is merge join and other becomes Hash join.
What do I have to do to get Merge join for both the joins as all 3 tables are large.
0
Comments
You may not get merge join for second join , if join column is different for second join. In your case first join uses A.property_1 and second join uses A.property_2 .
You may get mergejoin for both joins, only if you had used A.property_1 for both joins and there was not network send/receive betwen join1 and join2. For current query you can't achieve mergejoin for both queries.