[jira] [Commented] (TRAFODION-2765) MDAM is not considered when sometimes it should be

["David Wayne Birdsall (JIRA)" <ji...@apache.org>]

    [ 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)