[
https://issues.apache.org/jira/browse/TRAFODION-2765?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16192246#comment-16192246
]
David Wayne Birdsall commented on TRAFODION-2765:
-------------------------------------------------
Here is a script that reproduces the problem.
?section create
drop table if exists tda;
CREATE TABLE tda
(
A INT NO DEFAULT NOT NULL NOT DROPPABLE
SERIALIZED
, B INT NO DEFAULT NOT NULL NOT DROPPABLE
SERIALIZED
, C INT NO DEFAULT NOT NULL NOT DROPPABLE
SERIALIZED
, D INT NO DEFAULT NOT NULL NOT DROPPABLE
SERIALIZED
, PRIMARY KEY (A ASC, B ASC, C ASC)
)
SALT USING 4 PARTITIONS on (A)
;
?section populate
-- UEC of tda.a is 100, tda.b is 10, tda.c is 2000
upsert using load into tda
select x1+10*x2,
x3,
x4+10*x5+100*x6+1000*x7, x1+x2+10*x3+x4+10*x5+100*x6+1000*x7
-- the from clause below creates 2,000,000 rows, the cross product of
-- 6 copies of { 0, ... 9 } and one copy of { 0, 1 }
from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) T(x1)
transpose 0,1,2,3,4,5,6,7,8,9 as x2
transpose 0,1,2,3,4,5,6,7,8,9 as x3
transpose 0,1,2,3,4,5,6,7,8,9 as x4
transpose 0,1,2,3,4,5,6,7,8,9 as x5
transpose 0,1,2,3,4,5,6,7,8,9 as x6
transpose 0,1 as x7;
update statistics for table tda on every column;
?section testit
prepare s1 from select * from tda where a = 5 and c = 20;
In this example, we have a query with an equality predicate on the second key
column, A. There is an inferred equality predicate on the leading key column,
"_SALT_", since it is a function of A.
In this example, the code in ScanOptimizer::useSimpleFileScanOptimizer does not
consider MDAM.
> MDAM is not considered when sometimes it should be
> --------------------------------------------------
>
> Key: TRAFODION-2765
> URL: https://issues.apache.org/jira/browse/TRAFODION-2765
> Project: Apache Trafodion
> Issue Type: Bug
> Components: sql-cmp
> Affects Versions: 2.3-incubating
> Environment: All
> Reporter: David Wayne Birdsall
> Assignee: David Wayne Birdsall
>
> The Optimizer has logic in method ScanOptimizer::useSimpleFileScanOptimizer
> that tries to determine scenarios where a single subset access will clearly
> be optimal, and in those scenarios does not consider MDAM. An example would
> be a query whose only key predicates are equality predicates on a leading
> subset of the key columns.
> However, the logic in ScanOptimizer::useSimpleFileScanOptimizer sometimes
> rules out considering MDAM when it would be beneficial to do so. An example
> is a query with equality predicates on the first two columns of the key, no
> predicates on the third, and equality on the fourth. When the UEC of the
> third column is small, MDAM is likely to be the more efficient plan and
> should be considered.
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
