Github user sureshsubbiah commented on a diff in the pull request:
https://github.com/apache/incubator-trafodion/pull/1051#discussion_r111516680
--- Diff: core/sql/optimizer/RelExpr.cpp ---
@@ -9055,19 +9056,33 @@ void Scan::setTableAttributes(CANodeId nodeId)
setBaseCardinality(MIN_ONE (tableDesc->getNATable()->getEstRowCount())) ;
}
-NABoolean Scan::updateableIndex(IndexDesc *idx)
+NABoolean Scan::updateableIndex(IndexDesc *idx, ValueIdSet& preds,
+ NABoolean & needsHalloweenProtection)
{
//
- // Returns TRUE if the index (idx) can be used for a scan during
- // an UPDATE. Halloween problem is protected with a sort using
- // Scan::requiresHalloweenForUpdateUsingIndexScan().
- // Returns FALSE only for certain embedded updates now.
+ // Returns TRUE if the index (idx) can be used for a scan during an
UPDATE.
+ // Returns TRUE with needsHalloweenProtection also set to TRUE, if the
index
+ // needs a blocking sort for Halloween protection
+ // Otherwise, returns FALSE to prevent use of this index.
+ // Using the index in this case requires Halloween protection, but is
likely
+ // inefficient since there are no useful preds on the index key columns,
so
+ // we don't add this index to list of candidates.
//
- if (!getGroupAttr()->isEmbeddedUpdate())
- return TRUE ;
-
+ // The conditions of when this index returns TRUE can also be expressed
as
+ // returns true for an index if it is
+ // a) a unique/clustering index or
+ // b) has a unique predicate on its key or
+ // c) has an equals or range predicate on all the index columns that
--- End diff --
I am sorry @zellerh. I misunderstood the comment. I did not realize you
were asking to check if these two examples work as explained in this comment. I
tested them now and get these results. They do show the desired behaviour with
the current change. I am certain the alternate way discussed above will also
lead to the desired result. Thank you.
create table tbl (k int not null primary key, a int, b int) ;
create index ixa on tbl(a) ;
create index ixb on tbl(b) ;
control query shape nested_join(nested_join(nested_join(cut,cut),cut),cut) ;
prepare s1 from update tbl set b=b+1 where a > 10;
explain options 'f' s1 ;
explain options 'f' s1 ;
LC RC OP OPERATOR OPT DESCRIPTION CARD
---- ---- ---- -------------------- -------- --------------------
---------
10 . 11 root x
6.59E+001
6 9 10 nested_join
6.59E+001
7 8 9 blocked_union
2.00E+000
. . 8 trafodion_vsbb_upser IXB
1.00E+000
. . 7 trafodion_vsbb_delet IXB
1.00E+000
4 5 6 nested_join
3.29E+001
. . 5 trafodion_update TBL
1.00E+000
1 3 4 nested_join
3.29E+001
2 . 3 probe_cache
1.00E+000
. . 2 trafodion_vsbb_scan TBL
1.00E+000
. . 1 trafodion_index_scan IXA
3.29E+001
--- SQL operation complete.
control query shape nested_join(nested_join(cut,cut),cut) ;
prepare s2 from update tbl set b=b+1 where b > 10;
>>explain options 'f' s2 ;
LC RC OP OPERATOR OPT DESCRIPTION CARD
---- ---- ---- -------------------- -------- --------------------
---------
8 . 9 root x
6.69E+001
4 7 8 nested_join
6.59E+001
5 6 7 blocked_union
2.00E+000
. . 6 trafodion_vsbb_upser IXB
1.00E+000
. . 5 trafodion_vsbb_delet IXB
1.00E+000
2 3 4 nested_join
3.29E+001
. . 3 trafodion_update TBL
1.00E+000
1 . 2 sort
3.29E+001
. . 1 trafodion_index_scan IXB
3.29E+001
--- SQL operation complete.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at [email protected] or file a JIRA ticket
with INFRA.
---
