[jira] [Created] (TRAFODION-2280) Query optimizations based on uniqueness don't work on salted tables

["Hans Zeller (JIRA)" <ji...@apache.org>]

Hans Zeller created TRAFODION-2280:
--------------------------------------

             Summary: 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:

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




--
This message was sent by Atlassian JIRA
(v6.3.4#6332)