You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@trafodion.apache.org by "Suresh Subbiah (JIRA)" <ji...@apache.org> on 2017/04/07 02:35:41 UTC
[jira] [Commented] (TRAFODION-2574) Index plan not chosen for
UPDATE when WHERE clause and SET clause are on the same index column
[ 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)