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

Yogi_Baba

 

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

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

eli_revach

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

Yogi_Baba

Thanks. I will try the hint option.

 

best regards,

Yogi