[
https://issues.apache.org/jira/browse/TRAFODION-1562?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15655336#comment-15655336
]
ASF GitHub Bot commented on TRAFODION-1562:
-------------------------------------------
Github user sandhyasun commented on a diff in the pull request:
https://github.com/apache/incubator-trafodion/pull/826#discussion_r87498421
--- Diff: core/sql/optimizer/BindRelExpr.cpp ---
@@ -10383,6 +10423,310 @@ NABoolean
Insert::isUpsertThatNeedsMerge(NABoolean isAlignedRowFormat, NABoolean
return FALSE;
}
+#ifdef __ignore
+// take an insert(src) node and transform it into
+// a tuple_flow with old/new rows flowing to the IM tree.
+// with a newly created input_scan
+RelExpr* Insert::xformUpsertToEfficientTreeNoDup(BindWA *bindWA)
+{
+ NATable *naTable = bindWA->getNATable(getTableName());
+ if (bindWA->errStatus())
+ return NULL;
+ if ((naTable->getViewText() != NULL) && (naTable->getViewCheck()))
+ {
+ *CmpCommon::diags() << DgSqlCode(-3241)
+ << DgString0(" View with check option not allowed.");
+ bindWA->setErrStatus();
+ return NULL;
+ }
+
+ RelExpr *topNode = this;
+ // Create a new BindScope, to encompass the new nodes
+ // upsert(left_join(input_scan, tuple))
+ // and any inlining nodes that will be created. Any values the upsert
+ // and children will need from src will be marked as outer references in
that
+ // new BindScope. We assume that "src" is already bound.
+ ValueIdSet currOuterRefs = bindWA->getCurrentScope()->getOuterRefs();
+
+ CMPASSERT(child(0)->nodeIsBound());
+
+ BindScope *upsertScope = bindWA->getCurrentScope();
+
+ // columns of the target table
+ const ValueIdList &tableCols = updateToSelectMap().getTopValues();
+ const ValueIdList &sourceVals = updateToSelectMap().getBottomValues();
+
+ // create a Join node - left join of the base table columns with the
columns to be upserted.
+ // columns of the target table
+ CMPASSERT(child(0)->nodeIsBound());
+
+ Scan * targetTableScan =
+ new (bindWA->wHeap())
+ Scan(CorrName(getTableDesc()->getCorrNameObj(), bindWA->wHeap()));
+
+
+ //join predicate between source columns and target table.
+ ItemExpr * keyPred = NULL;
+ ItemExpr * keyPredPrev = NULL;
+ BaseColumn* baseCol;
+ ColReference * targetColRef;
+ int predCount = 0;
+ ValueIdSet newOuterRefs;
+ ItemExpr * pkeyValPrev;
+ ItemExpr * pkeyVals;
+ for (CollIndex i = 0; i < tableCols.entries(); i++)
+ {
+ baseCol = (BaseColumn *)(tableCols[i].getItemExpr()) ;
+ if (baseCol->getNAColumn()->isSystemColumn())
+ continue;
+
+ targetColRef = new(bindWA->wHeap()) ColReference(
+ new(bindWA->wHeap())
ColRefName(
+
baseCol->getNAColumn()->getFullColRefName(), bindWA->wHeap()));
+
+
+ if (baseCol->getNAColumn()->isClusteringKey())
+ {
+ // create a join/key predicate between source and target table,
+ // on the clustering key columns of the target table, making
+ // ColReference nodes for the target table, so that we can bind
+ // those to the new scan
+ keyPredPrev = keyPred;
+ keyPred = new (bindWA->wHeap())
+ BiRelat(ITM_EQUAL, targetColRef,
+ sourceVals[i].getItemExpr(),
+ baseCol->getType().supportsSQLnull());
+ predCount++;
+ if (predCount > 1)
+ {
+ keyPred = new(bindWA->wHeap()) BiLogic(ITM_AND,
+ keyPredPrev,
+ keyPred);
+ }
+ pkeyValPrev = pkeyVals;
+
+ pkeyVals = tableCols[i].getItemExpr();
+ if (i > 0)
+ {
+ pkeyVals = new(bindWA->wHeap()) ItemList(pkeyVals,pkeyValPrev);
+
+ }
+ }
+
+ }
+
+ // Map the table's primary key values to the source lists key values
+ ValueIdList tablePKeyVals = NULL;
+ ValueIdList sourcePKeyVals = NULL;
+
+ pkeyVals->convertToValueIdList(tablePKeyVals,bindWA,ITM_ITEM_LIST);
+ updateToSelectMap().mapValueIdListDown(tablePKeyVals,sourcePKeyVals);
+
+ Join *lj = new(bindWA->wHeap())
Join(child(0),targetTableScan,REL_LEFT_JOIN,keyPred);
+ lj->doNotTransformToTSJ();
+ lj->setTSJForWrite(TRUE);
+ bindWA->getCurrentScope()->xtnmStack()->createXTNM();
+ RelExpr *boundLJ = lj->bindNode(bindWA);
+ if (bindWA->errStatus())
+ return NULL;
+ bindWA->getCurrentScope()->xtnmStack()->removeXTNM();
+ setChild(0,boundLJ);
+ topNode = handleInlining(bindWA,topNode);
+
+
+ return topNode;
+}
+#endif
+// take an insert(src) node and transform it into
+// a tuple_flow with old/new rows flowing to the IM tree.
+// with a newly created sequence node used to eliminate duplicates.
+/*
+ NJ
+ / \
+ Sequence NJ
+ / \
+ Left Join IM Tree
+ / \
+ / \
+Input Tuplelist Target Table Scan
+or select list
+*/
+
+RelExpr* Insert::xformUpsertToEfficientTree(BindWA *bindWA)
+{
+ NATable *naTable = bindWA->getNATable(getTableName());
+ if (bindWA->errStatus())
+ return NULL;
+ if ((naTable->getViewText() != NULL) && (naTable->getViewCheck()))
+ {
+ *CmpCommon::diags() << DgSqlCode(-3241)
+ << DgString0(" View with check option not allowed.");
+ bindWA->setErrStatus();
+ return NULL;
+ }
+
+ RelExpr *topNode = this;
+
+ CMPASSERT(child(0)->nodeIsBound());
+
+ BindScope *upsertScope = bindWA->getCurrentScope();
+ // Create a new BindScope, to encompass the new nodes
+ // upsert(left_join(input_scan, tuple))
+ // and any inlining nodes that will be created. Any values the upsert
+ // and children will need from src will be marked as outer references in
that
+ // new BindScope. We assume that "src" is already bound.
+ ValueIdSet currOuterRefs = bindWA->getCurrentScope()->getOuterRefs();
+ // Save the current RETDesc.
+ RETDesc *prevRETDesc = bindWA->getCurrentScope()->getRETDesc();
+
+ // columns of the target table
+ const ValueIdList &tableCols = updateToSelectMap().getTopValues();
+ const ValueIdList &sourceVals = updateToSelectMap().getBottomValues();
+
+ // create a Join node - left join of the base table columns with the
columns to be upserted.
+ // columns of the target table
+ CMPASSERT(child(0)->nodeIsBound());
+
+ Scan * targetTableScan =
+ new (bindWA->wHeap())
+ Scan(CorrName(getTableDesc()->getCorrNameObj(), bindWA->wHeap()));
+
+
+ //join predicate between source columns and target table.
+ ItemExpr * keyPred = NULL;
+ ItemExpr * keyPredPrev = NULL;
+ BaseColumn* baseCol;
+ ColReference * targetColRef;
+ int predCount = 0;
+ ValueIdSet newOuterRefs;
+ ItemExpr * pkeyValPrev;
+ ItemExpr * pkeyVals = NULL;
+ for (CollIndex i = 0; i < tableCols.entries(); i++)
+ {
+ baseCol = (BaseColumn *)(tableCols[i].getItemExpr()) ;
+ if (baseCol->getNAColumn()->isSystemColumn())
+ continue;
+
+ targetColRef = new(bindWA->wHeap()) ColReference(
+ new(bindWA->wHeap())
ColRefName(
+
baseCol->getNAColumn()->getFullColRefName(), bindWA->wHeap()));
+
+
+ if (baseCol->getNAColumn()->isClusteringKey())
--- End diff --
The system columns like SALT and DIVISION don't need to be part of the join
key, I suppose. Also I checked that we don't do this transformation in case the
table has a syskey. Method Insert::isUpsertThatNeedsMerge(() ensures this. So I
think we are ok with this ..
> Changes in query tree when the upsert command is transformed into merge
> -----------------------------------------------------------------------
>
> Key: TRAFODION-1562
> URL: https://issues.apache.org/jira/browse/TRAFODION-1562
> Project: Apache Trafodion
> Issue Type: Sub-task
> Components: sql-cmp
> Reporter: Selvaganesan Govindarajan
> Assignee: Sandhya Sundaresan
> Attachments: BatchUpsertTransformation.pdf
>
>
> to improve the performance as explained in the main JIRA
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
