We're Moving!

The Vertica Forum is moving to a new OpenText Analytics Database (Vertica) Community.

Join us there to post discussion topics, learn about

product releases, share tips, access the blog, and much more.

Create My New Community Account Now


Query executes a Hash Join instead of Merge Join as join column names dont match — Vertica Forum

Query executes a Hash Join instead of Merge Join as join column names dont match

 

-----------------------------------------------------------------------------------------------------------------------------------------------------

Query executes a Hash Join instead of Merge Join when column names of the join clause are named differently

--------------------------------------------------------------------------------------------------------------------------------------------------

 

 

Hello All,

 

I have a following situation.

 

CREATE TABLE  TEST1 

 (

C1   varchar(10),

C2   int 

C3  varchar(50) 

)  order By  C1, C2

segmented by (C1) ALL nodes kSAFE;

 

CREATE TABLE  TEST2

 (

D1   varchar(10),

 

 

D2   int 

D3  varchar(50) 

)  order By  D1, D2

segmented by (D1) ALL nodes kSAFE;

 

 

QUERY :  

 

select count(*) from TEST1  a   

LEFT OUTER JOIN  TEST2   ON a.C1=B.C1   AND a.C2=B.D2  

 

 

I expected that this end up in MERGEJOIN but that is not happening.    Please let me know if you have come across this issue.    Also I am not sure if this is Vertica's normal behavior that it expects column name to match so that the optimizer can understand.

 

 

best regards,

Yogi

Comments

  • Hi If the data set is small, it's possible that a hash join can process faster , then Sort Merge . The optimizer is able to run Sort Merge when the join columns name are not the same , your can play with the below hint to verify that : SELECT /*+ add_vertica_options(OPT, PLAN_SORT_MERGE_JOIN) */ Thanks
  • Thanks. I will try the hint option.

     

    best regards,

    Yogi

Leave a Comment

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