[
https://issues.apache.org/jira/browse/TRAFODION-1562?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15650310#comment-15650310
]
Sandhya Sundaresan commented on TRAFODION-1562:
-----------------------------------------------
The goal of the fix is to ensure the upserts go through as rowset operations so
the row-at-a-time penalty is removed. For this we use the LEAD olap
functionality. We eliminate duplicates in the input so it never reaches the
upsert node and the IM tree. This improves the performance for the tables and
non unique index considerably. For unique indexes since we do a checkAndPut to
insert into the unique indexes the performance benefit is not that significant.
NJ
/ \
Sequence NJ
/ \
Left Join IM Tree
/ \
/ \
Input Tuplelist Target Table Scan
or select list
The rational for introducing the LEAD (Sequence operator) in the plan is as
follows . See how the LEAD operator can be useful in eliminating the output of
the LeftJoin in eliminating duplicates :
>>create table t15 (c1 int not null, c2 int not null , c3 int, c4 int, c5 int);
--- SQL operation complete.
>>>>insert into t15 values
>>>>(1,2,1,1,1),(1,2,3,4,5),(1,3,1,1,1),(1,4,1,1,1),(1,4,3,4,5),(1,5,1,1,1);
--- 6 row(s) inserted.
>>
>>select * from t15;
C1 C2 C3 C4 C5
----------- ----------- ----------- ----------- -----------
1 2 1 1 1
1 2 3 4 5
1 3 1 1 1
1 4 1 1 1
1 4 3 4 5
1 5 1 1 1
--- 6 row(s) selected.
>>select * from (select c1,c2,c3,c4,c5, lead(999) over (partition by c1,c2) L
>>from t15) ;
C1 C2 C3 C4 C5 L
----------- ----------- ----------- ----------- ----------- ------
1 2 1 1 1 999
1 2 3 4 5 ?
1 3 1 1 1 ?
1 4 3 4 5 999
1 4 6 7 8 999
1 4 1 1 1 ?
1 5 1 1 1 ?
--- 7 row(s) selected.
>>select * from (select c1,c2,c3,c4,c5, lead(999) over (partition by c1,c2) L
>>from t15) where L is NULL;
C1 C2 C3 C4 C5 L
----------- ----------- ----------- ----------- ----------- ------
1 2 3 4 5 ?
1 3 1 1 1 ?
1 4 1 1 1 ?
1 5 1 1 1 ?
--- 4 row(s) selected.
> 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)
