[ https://issues.apache.org/jira/browse/TRAFODION-2574?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15960188#comment-15960188 ]
Suresh Subbiah commented on TRAFODION-2574: ------------------------------------------- Here is the plan we now get for these four statements (with a fix) >>control query shape nested_join(nested_join(cut,cut),cut); --- SQL operation complete. >>prepare s1 from update tbl set b = 'haha' where b = 'sad' ; >>explain options 'f' s1 ; LC RC OP OPERATOR OPT DESCRIPTION CARD ---- ---- ---- -------------------- -------- -------------------- --------- 14 . 15 root x 4.00E+000 6 13 14 nested_join 4.00E+000 9 12 13 merge_union 4.00E+000 10 11 12 blocked_union 2.00E+000 . . 11 trafodion_insert TBL_IDX1_B 1.00E+000 . . 10 trafodion_vsbb_delet TBL_IDX1_B 1.00E+000 7 8 9 blocked_union 2.00E+000 . . 8 trafodion_insert TBL_IDX1_AB 1.00E+000 . . 7 trafodion_vsbb_delet TBL_IDX1_AB 1.00E+000 4 5 6 nested_join 1.00E+000 . . 5 trafodion_update TBL 1.00E+000 1 3 4 nested_join 1.00E+000 2 . 3 probe_cache 1.00E+000 . . 2 trafodion_vsbb_scan TBL 1.00E+000 . . 1 trafodion_index_scan TBL_IDX1_B 1.00E+000 --- SQL operation complete. -- should choose tbl_idx1_ab >>prepare s2 from update tbl set a = 'haha' where a = 'sad' ; --- SQL command prepared. >>explain options 'f' s2 ; LC RC OP OPERATOR OPT DESCRIPTION CARD ---- ---- ---- -------------------- -------- -------------------- --------- 7 . 8 root x 2.00E+000 3 6 7 nested_join 2.00E+000 4 5 6 blocked_union 2.00E+000 . . 5 trafodion_insert TBL_IDX1_AB 1.00E+000 . . 4 trafodion_vsbb_delet TBL_IDX1_AB 1.00E+000 1 2 3 nested_join 1.00E+000 . . 2 trafodion_update TBL 1.00E+000 . . 1 trafodion_index_scan TBL_IDX1_AB 1.00E+000 --- SQL operation complete. >>prepare s3 from update tbl set b = 'haha' where b > 'sad' ; --- SQL command prepared. >>explain options 'f' s3 ; LC RC OP OPERATOR OPT DESCRIPTION CARD ---- ---- ---- -------------------- -------- -------------------- --------- 15 . 16 root x 4.00E+000 7 14 15 nested_join 4.00E+000 10 13 14 merge_union 4.00E+000 11 12 13 blocked_union 2.00E+000 . . 12 trafodion_insert TBL_IDX1_B 1.00E+000 . . 11 trafodion_vsbb_delet TBL_IDX1_B 1.00E+000 8 9 10 blocked_union 2.00E+000 . . 9 trafodion_insert TBL_IDX1_AB 1.00E+000 . . 8 trafodion_vsbb_delet TBL_IDX1_AB 1.00E+000 5 6 7 nested_join 1.00E+000 . . 6 trafodion_update TBL 1.00E+000 4 . 5 sort 1.00E+000 1 3 4 nested_join 1.00E+000 2 . 3 probe_cache 1.00E+000 . . 2 trafodion_vsbb_scan TBL 1.00E+000 . . 1 trafodion_index_scan TBL_IDX1_B 1.00E+000 --- SQL operation complete. >>prepare s4 from update tbl set a = 'haha' where a > 'sad' ; --- SQL command prepared. >>explain options 'f' s4 ; LC RC OP OPERATOR OPT DESCRIPTION CARD ---- ---- ---- -------------------- -------- -------------------- --------- 8 . 9 root x 2.00E+000 4 7 8 nested_join 2.00E+000 5 6 7 blocked_union 2.00E+000 . . 6 trafodion_insert TBL_IDX1_AB 1.00E+000 . . 5 trafodion_vsbb_delet TBL_IDX1_AB 1.00E+000 2 3 4 nested_join 1.00E+000 . . 3 trafodion_update TBL 1.00E+000 1 . 2 sort 1.00E+000 . . 1 trafodion_index_scan TBL_IDX1_AB 1.00E+000 --- SQL operation complete. >> > Index plan not chosen for UPDATE when WHERE clause and SET clause are on the > same index column > ---------------------------------------------------------------------------------------------- > > Key: TRAFODION-2574 > URL: https://issues.apache.org/jira/browse/TRAFODION-2574 > Project: Apache Trafodion > Issue Type: Bug > Components: sql-cmp > Affects Versions: any > Reporter: Suresh Subbiah > Assignee: Suresh Subbiah > Fix For: 2.2-incubating > > > create table tbl ( > k1 int not null, > k2 int not null, > ts timestamp not null, > a char(10), > b varchar(30), > c largeint, > primary key (k1,k2,ts)) > salt using 8 partitions > division by (date_trunc('MONTH', ts)) ; > upsert using load into tbl > select num/1000, num, DATEADD(SECOND,-num,CURRENT_TIMESTAMP),cast(num as > char(10)), cast(num as varchar(30)), num*1000 > from (select > 10000000*x10000000+1000000*x1000000+100000*x100000+10000*x10000+1000*x1000+100*x100+10*x10+x1 > as num > from (values (0)) seed(c) > transpose 0,1,2,3,4,5,6,7,8,9 as x1 > transpose 0,1,2,3,4,5,6,7,8,9 as x10 > transpose 0,1,2,3,4,5,6,7,8,9 as x100 > transpose 0,1,2,3,4,5,6,7,8,9 as x1000 > transpose 0,1,2,3,4,5,6,7,8,9 as x10000 > transpose 0,1,2,3,4,5,6,7,8,9 as x100000 > transpose 0,1,2,3,4,5,6,7,8,9 as x1000000 > transpose 0,1,2,3,4,5,6,7,8,9 as x10000000 > ) T > ; > create index tbl_idx1_b on tbl(b) salt like table; > create index tbl_idx1_ab on tbl(a,b) salt like table; > update statistics for table tbl on every column sample; > -- should choose tbl_idx1_b > update tbl set b = 'haha' where b = 'sad' ; > -- should choose tbl_idx1_b, at least with CQS > update tbl set b = 'haha' where b > 'sad' ; > -- should choose tbl_idx1_ab > update tbl set a = 'haha' where a = 'sad' ; > -- should choose tbl_idx1_ab, at least with CQS > update tbl set a = 'haha' where a > 'sad' ; -- This message was sent by Atlassian JIRA (v6.3.15#6346)