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)
