[ 
https://issues.apache.org/jira/browse/TRAFODION-2280?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Work on TRAFODION-2280 started by Hans Zeller.
----------------------------------------------
> 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)

Reply via email to