[
https://issues.apache.org/jira/browse/TRAFODION-2813?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16260007#comment-16260007
]
ASF GitHub Bot commented on TRAFODION-2813:
-------------------------------------------
GitHub user DaveBirdsall opened a pull request:
https://github.com/apache/incubator-trafodion/pull/1306
[TRAFODION-2813] Ignore leading salt columns when checking index
compatibility
See the JIRA for fix details.
You can merge this pull request into a Git repository by running:
$ git pull https://github.com/DaveBirdsall/incubator-trafodion Trafodion2813
Alternatively you can review and apply these changes as the patch at:
https://github.com/apache/incubator-trafodion/pull/1306.patch
To close this pull request, make a commit to your master/trunk branch
with (at least) the following in the commit message:
This closes #1306
----
commit 25e64a8fa6a0c91f9451f7b6fa10e52ac03d58c3
Author: Dave Birdsall <dbirds...@apache.org>
Date: 2017-11-20T23:14:29Z
[TRAFODION-2813] Ignore leading salt columns when checking index
compatibility
----
> Salting + heuristics prevent MDAM plan on base table from being considered
> --------------------------------------------------------------------------
>
> Key: TRAFODION-2813
> URL: https://issues.apache.org/jira/browse/TRAFODION-2813
> Project: Apache Trafodion
> Issue Type: Bug
> Components: sql-cmp
> Affects Versions: 2.3-incubating
> Environment: All
> Reporter: David Wayne Birdsall
> Assignee: David Wayne Birdsall
>
> When a salted table also has salted indexes, it may happen that a query that
> has a good MDAM plan on the base table gets a full scan on a salted index
> instead.
> The problem is that there is a defective heuristic that rules out base table
> access before we get to the costing code. So the costing code only sees one
> access path, namely the index, and (correctly) picks a full scan on it.
> The script below reproduces the problem:
> ?section setup
> drop table if exists trafodion.seabase.t1 cascade;
> CREATE TABLE TRAFODION.SEABASE.T1
> (
> key1 NUMERIC(9, 0) NO DEFAULT NOT NULL NOT
> DROPPABLE NOT SERIALIZED
> , key2 VARCHAR(64) CHARACTER SET ISO88591 COLLATE
> DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE NOT SERIALIZED
> , key3 TIMESTAMP(6) NO DEFAULT NOT NULL NOT
> DROPPABLE NOT SERIALIZED
> , nonkey VARCHAR(1024) CHARACTER SET ISO88591
> COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
> , PRIMARY KEY (key1 ASC, key2 ASC, key3 ASC)
> )
> SALT USING 4 PARTITIONS
> ON (key2)
> DIVISION BY (DATE_TRUNC('HOUR',key3)
> NAMED AS ("_DIVISION_1_"))
> ATTRIBUTES ALIGNED FORMAT
> HBASE_OPTIONS
> (
> DATA_BLOCK_ENCODING = 'FAST_DIFF',
> MEMSTORE_FLUSH_SIZE = '1073741824'
> )
> ;
> upsert using load into TRAFODION.SEABASE.T1
> select 50000 + c0,
> 'user ' || cast(c5 + 10*c0 + 100*c1 + 1000*c2 + 10000*c3 as varchar(20)),
> timestamp '2017-07-01 12:00:00.000000' + cast(c1 + 10*c2 + 100*c3 + 1000*c4
> + 1000*c5 as interval minute(6)),
> 'valid prod ' || cast(7*c3 + 4*c4 + 11*c2 as varchar(20))
> from (values(1)) t
> transpose 0,1,2,3,4,5,6,7,8,9 as c0
> transpose 0,1,2,3,4,5,6,7,8,9 as c1
> transpose 0,1,2,3,4,5,6,7,8,9 as c2
> transpose 0,1,2,3,4,5,6,7,8,9 as c3
> transpose 0,1,2,3,4,5,6,7,8,9 as c4
> transpose 0,1,2,3,4,5,6,7,8,9 as c5;
> update statistics for table TRAFODION.SEABASE.T1 on every column;
> CREATE INDEX T1_IDX1 ON TRAFODION.SEABASE.T1
> (
> key2 ASC
> , key3 ASC
> )
> ATTRIBUTES ALIGNED FORMAT
> HBASE_OPTIONS
> (
> DATA_BLOCK_ENCODING = 'FAST_DIFF',
> MEMSTORE_FLUSH_SIZE = '1073741824'
> )
> SALT LIKE TABLE
> ;
> ?section queries
> prepare s1 from
> SELECT key3, key2 FROM trafodion.seabase.t1 WHERE key1 = 50001 AND key3
> >= to_date(cast (20170705080000 as char(20)),'YYYYMMDDHH24MISS') AND key3
> <= to_date(cast (20170705085959 as char(20)),'YYYYMMDDHH24MISS') ;
> explain s1;
> In this example, S1 gets full scans on the index, even though an MDAM plan on
> the base table would be far more efficient.
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
