You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@trafodion.apache.org by "David Wayne Birdsall (JIRA)" <ji...@apache.org> on 2017/11/22 17:32:00 UTC
[jira] [Resolved] (TRAFODION-2813) Salting + heuristics prevent
MDAM plan on base table from being considered
[ https://issues.apache.org/jira/browse/TRAFODION-2813?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
David Wayne Birdsall resolved TRAFODION-2813.
---------------------------------------------
Resolution: Fixed
Fix Version/s: 2.3-incubating
> 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
> Fix For: 2.3-incubating
>
>
> 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)