[ https://issues.apache.org/jira/browse/TRAFODION-2280?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Hans Zeller updated TRAFODION-2280: ----------------------------------- Description: Rohit Jain noticed this problem. When we have an IN subquery that produces a table with unique values, then we should use an inner join in the query plan. However, we see a semi-join when the subquery references a salted table. The problem is that the uniqueness constraint generated for salted tables is not optimal. Another example is a groupby on a unique column - the optimizer should eliminate the groupby, but this does not happen for salted tables. Here are some example queries to demonstrate the problem: {noformat} create table tsalt(a integer not null primary key, b integer) salt using 4 partitions; create table tnosalt(a integer not null primary key, b integer); insert into tsalt values (1,1), (2,2), (3,3); insert into tnosalt select * from tsalt; prepare s1 from -- display select * from tnosalt where b in (select a from tsalt); explain options 'f' s1; -- uses a semi-join, but a join would be sufficient prepare s2 from -- display select * from tnosalt where b in (select a from tnosalt); explain options 'f' s2; -- uses a regular inner join explain options 'f' select distinct a from tsalt; -- the plan has an unnecessary group by explain options 'f' select distinct a from tnosalt; -- the groupby is eliminated {noformat} was: Rohit Jain noticed this problem. When we have an IN subquery that produces a table with unique values, then we should use an inner join in the query plan. However, we see a semi-join when the subquery references a salted table. The problem is that the uniqueness constraint generated for salted tables is not optimal. Another example is a groupby on a unique column - the optimizer should eliminate the groupby, but this does not happen for salted tables. Here are some example queries to demonstrate the problem: create table tsalt(a integer not null primary key, b integer) salt using 4 partitions; create table tnosalt(a integer not null primary key, b integer); insert into tsalt values (1,1), (2,2), (3,3); insert into tnosalt select * from tsalt; prepare s1 from -- display select * from tnosalt where b in (select a from tsalt); explain options 'f' s1; -- uses a semi-join, but a join would be sufficient prepare s2 from -- display select * from tnosalt where b in (select a from tnosalt); explain options 'f' s2; -- uses a regular inner join explain options 'f' select distinct a from tsalt; -- the plan has an unnecessary group by explain options 'f' select distinct a from tnosalt; -- the groupby is eliminated > Query optimizations based on uniqueness don't work on salted tables > ------------------------------------------------------------------- > > Key: TRAFODION-2280 > URL: https://issues.apache.org/jira/browse/TRAFODION-2280 > Project: Apache Trafodion > Issue Type: Bug > Components: sql-cmp > Affects Versions: 2.0-incubating > Environment: Any > Reporter: Hans Zeller > Assignee: Hans Zeller > Fix For: 2.1-incubating > > > Rohit Jain noticed this problem. When we have an IN subquery that produces a > table with unique values, then we should use an inner join in the query plan. > However, we see a semi-join when the subquery references a salted table. The > problem is that the uniqueness constraint generated for salted tables is not > optimal. Another example is a groupby on a unique column - the optimizer > should eliminate the groupby, but this does not happen for salted tables. > Here are some example queries to demonstrate the problem: > {noformat} > create table tsalt(a integer not null primary key, b integer) salt using 4 > partitions; > create table tnosalt(a integer not null primary key, b integer); > insert into tsalt values (1,1), (2,2), (3,3); > insert into tnosalt select * from tsalt; > prepare s1 from > -- display > select * from tnosalt where b in (select a from tsalt); > explain options 'f' s1; > -- uses a semi-join, but a join would be sufficient > prepare s2 from > -- display > select * from tnosalt where b in (select a from tnosalt); > explain options 'f' s2; > -- uses a regular inner join > explain options 'f' > select distinct a from tsalt; > -- the plan has an unnecessary group by > explain options 'f' > select distinct a from tnosalt; > -- the groupby is eliminated > {noformat} -- This message was sent by Atlassian JIRA (v6.3.4#6332)