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.

Comments

  • skamatskamat Administrator

    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.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.