[
https://issues.apache.org/jira/browse/TRAFODION-2280?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Hans Zeller resolved TRAFODION-2280.
------------------------------------
Resolution: Fixed
Code committed on 10/31/2016 with
https://github.com/apache/incubator-trafodion/pull/794.
> 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)
