Hans Zeller created TRAFODION-2368:
--------------------------------------
Summary: 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
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)
