[
https://issues.apache.org/jira/browse/TRAFODION-2368?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Hans Zeller reassigned TRAFODION-2368:
--------------------------------------
Assignee: Hans Zeller
> Enable merge join for two salted tables with matching keys
> ----------------------------------------------------------
>
> Key: TRAFODION-2368
> URL: https://issues.apache.org/jira/browse/TRAFODION-2368
> Project: Apache Trafodion
> Issue Type: Improvement
> Components: sql-cmp
> Affects Versions: 2.1-incubating
> Environment: Any
> Reporter: Hans Zeller
> Assignee: Hans Zeller
>
> This problem was pointed out by Eric Owhadi.
> When we join two tables with matching keys on those key columns, a merge join
> (without a sort) should be ideal. This is possible for unsalted tables and a
> serial plan, although the plan may have to be forced. It would be good if we
> could do the same with parallel plans and/or salted tables. One first case to
> tackle would be if the tables are salted the same way and if each ESP reads
> only one salt value, therefore getting the rows in the order of the original
> key columns.
> Two test cases:
> First, a simple serial plan, merge join (without sort) is possible:
> {code}
> drop table freqcols;
> create table freqcols(a int not null,
> b char(10) not null,
> f1 int,
> primary key(a,b));
> drop table nonfreqcols;
> create table nonfreqcols(a int not null,
> b char(10) not null,
> n1 int,
> primary key(a,b));
> insert into freqcols values(1,'a',1), (1,'b',1), (2,'a',2);
> insert into nonfreqcols values(1,'a',1), (1,'b',1), (2,'a',2);
> control query shape merge_join(scan,scan);
> prepare s from select * from freqcols natural join nonfreqcols;
> explain options 'f' s;
> {code}
> Next, two salted tables, merge join plan is not yet possible:
> {code}
> drop table freqcols;
> create table freqcols(a int not null,
> b char(10) not null,
> f1 int,
> primary key(a,b))
> salt using 4 partitions on(a);
> drop table nonfreqcols;
> create table nonfreqcols(a int not null,
> b char(10) not null,
> n1 int,
> primary key(a,b))
> salt using 4 partitions on(a);
> insert into freqcols values(1,'a',1), (1,'b',1), (2,'a',2);
> insert into nonfreqcols values(1,'a',1), (1,'b',1), (2,'a',2);
> cqd attempt_esp_parallelism 'maximum';
> control query shape esp_exchange(merge_join(scan,scan),4);
> prepare s from select * from freqcols natural join nonfreqcols;
> --error, incompatible shape
> {code}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
