[
https://issues.apache.org/jira/browse/TRAFODION-2813?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16261233#comment-16261233
]
ASF GitHub Bot commented on TRAFODION-2813:
-------------------------------------------
Github user zellerh commented on a diff in the pull request:
https://github.com/apache/incubator-trafodion/pull/1306#discussion_r152363289
--- Diff: core/sql/optimizer/IndexDesc.cpp ---
@@ -802,10 +802,30 @@ IndexProperty::compareIndexPromise(const
IndexProperty *ixProp) const
const IndexDesc * index = getIndexDesc();
const IndexDesc * otherIndex = ixProp->getIndexDesc();
- if ( ((IndexColumn
*)(index->getIndexKey()[0]).getItemExpr())->getDefinition() !=
- ((IndexColumn
*)(otherIndex->getIndexKey()[0]).getItemExpr())->getDefinition() )
-
- return INCOMPATIBLE;
+
+ // If the two indexes have differing leading columns, consider them
incompatible.
+ // For this check, we ignore the "_SALT_" column if both are salted.
+ CollIndex columnToCheck = 0;
+ NABoolean done = FALSE;
+ while (!done)
+ {
+ if (columnToCheck >= index->getIndexKey().entries())
+ return INCOMPATIBLE; // must be one of the indexes is just
"_SALT_" (seems unlikely actually)
+ else if (columnToCheck >= otherIndex->getIndexKey().entries())
+ return INCOMPATIBLE; // must be one of the indexes is just
"_SALT_" (seems unlikely actually)
+ else
+ {
+ IndexColumn * indexCol = (IndexColumn
*)(index->getIndexKey()[columnToCheck]).getItemExpr();
+ IndexColumn * otherIndexCol = (IndexColumn
*)(otherIndex->getIndexKey()[columnToCheck]).getItemExpr();
+ if ( indexCol->getNAColumn()->isSaltColumn() &&
+ otherIndexCol->getNAColumn()->isSaltColumn() )
--- End diff --
Right now, all we allow is DIVISION LIKE TABLE, the index must have the
same divisioning columns as the base table, if any. So, it's the same
restriction we have for salting.
> 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)
