[ https://issues.apache.org/jira/browse/TRAFODION-882?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15293540#comment-15293540 ]
David Wayne Birdsall commented on TRAFODION-882: ------------------------------------------------ Note that in order to support statistics on volatile tables (as in https://issues.apache.org/jira/browse/TRAFODION-2004), this issue needs to be fixed. This is because volatile tables are only visible in the session that created them. > LP Bug: 1409937 - Following update statistics, stats do not take effect > immediately. > ------------------------------------------------------------------------------------ > > Key: TRAFODION-882 > URL: https://issues.apache.org/jira/browse/TRAFODION-882 > Project: Apache Trafodion > Issue Type: Bug > Components: sql-cmp > Reporter: Julie Thai > Assignee: Selvaganesan Govindarajan > Priority: Critical > Fix For: 2.1-incubating > > > On 20150106 & 20150110 builds, immediately following an updatestats, query > plan generated does not seem to reflect the existence of stats. In a session, > create, & populate table, run updatestats, prepare query and exit. A serial > plan is generated and est cardinality is 100 for both tables. In a new > session, prepare the same query and a parallel plan is generated, where est > cardinality reflects stats. > FIRST SESSION: > >>create table tbl1( > +> t1_c1 integer unsigned > +> ,t1_c2 char(10) character set iso88591 > +> ,t1_c3 largeint > +> ,t1_c4 int > +> ,t1_c5 int > +> ,t1_id1 int not null > +> ,t1_id2 int not null) > +>hash partition > +>store by (t1_id1) > +>salt using 8 partitions > +>; > --- SQL operation complete. > >>load into tbl1 > +>select c1+c2*10+c3*100+c4*1000+c5*10000+c6*100000 > +> ,cast(c1+c2*10+c3*100+c4*1000+c5*10000+c6*100000 as char(10) character > set iso88591) > +> ,c1+c2*10+c3*100+c4*1000+c5*10000+c6*100000 > +> ,c1+c2*10+c3*100+c4*1000+c5*10000+c6*100000 > +> ,c1 > +> ,c1+c2*10+c3*100+c4*1000+c5*10000+c6*100000 > +> ,c1+c2*10+c3*100+c4*1000+c5*10000+c6*100000 > +>from (values(1)) t > +>transpose 0,1,2,3,4,5,6,7,8,9 as c1 > +>transpose 0,1,2,3,4,5,6,7,8,9 as c2 > +>transpose 0,1,2,3,4,5,6,7,8,9 as c3 > +>transpose 0,1,2,3,4,5,6,7,8,9 as c4 > +>transpose 0,1,2,3,4,5,6,7,8,9 as c5 > +>transpose 0,1,2,3,4,5,6,7,8,9 as c6 > +>; > Task: LOAD Status: Started Object: TRAFODION.ASCH.TBL1 > Task: CLEANUP Status: Started Object: TRAFODION.ASCH.TBL1 > Task: CLEANUP Status: Ended Object: TRAFODION.ASCH.TBL1 > Task: DISABLE INDEXE Status: Started Object: TRAFODION.ASCH.TBL1 > Task: DISABLE INDEXE Status: Ended Object: TRAFODION.ASCH.TBL1 > Task: PREPARATION Status: Started Object: TRAFODION.ASCH.TBL1 > Rows Processed: 1000000 > Task: PREPARATION Status: Ended ET: 00:00:23.512 > Task: COMPLETION Status: Started Object: TRAFODION.ASCH.TBL1 > Task: COMPLETION Status: Ended ET: 00:00:01.564 > Task: POPULATE INDEX Status: Started Object: TRAFODION.ASCH.TBL1 > Task: POPULATE INDEX Status: Ended ET: 00:00:03.479 > --- 1000000 row(s) loaded. > >> > >>update tbl1 set t1_c1=7777 where t1_id1 between 3000 and 6999; > --- 4000 row(s) updated. > >>update tbl1 set t1_c1=7777 where t1_id1 between 205000 and 206999; > --- 2000 row(s) updated. > >>update tbl1 set t1_c1=7777 where t1_id1 between 410000 and 411999; > --- 2000 row(s) updated. > >>update tbl1 set t1_c1=7777 where t1_id1 between 615000 and 615999; > --- 1000 row(s) updated. > >>update tbl1 set t1_c1=7777 where t1_id1 between 820000 and 820999; > --- 1000 row(s) updated. > >>update tbl1 set t1_c2='7777' where t1_id1 between 2500 and 6499; > --- 4000 row(s) updated. > >>update tbl1 set t1_c2='7777' where t1_id1 between 204000 and 205999; > --- 2000 row(s) updated. > >>update tbl1 set t1_c2='7777' where t1_id1 between 411000 and 412999; > --- 2000 row(s) updated. > >>update tbl1 set t1_c2='7777' where t1_id1 between 614500 and 615499; > --- 1000 row(s) updated. > >>update tbl1 set t1_c2='7777' where t1_id1 between 820500 and 821499; > --- 1000 row(s) updated. > >>update tbl1 set t1_c3=888 where t1_id1 between 2000 and 7999; > --- 6000 row(s) updated. > >>update tbl1 set t1_c3=888 where t1_id1 between 205000 and 205999; > --- 1000 row(s) updated. > >>update tbl1 set t1_c3=888 where t1_id1 between 410500 and 411499; > --- 1000 row(s) updated. > >>update tbl1 set t1_c3=888 where t1_id1 between 615500 and 616499; > --- 1000 row(s) updated. > >>update tbl1 set t1_c3=888 where t1_id1 between 820500 and 821499; > --- 1000 row(s) updated. > >>update tbl1 set t1_c4=999 where t1_id1 between 3000 and 6999; > --- 4000 row(s) updated. > >>update tbl1 set t1_c4=999 where t1_id1 between 205000 and 206999; > --- 2000 row(s) updated. > >>update tbl1 set t1_c4=999 where t1_id1 between 410000 and 411999; > --- 2000 row(s) updated. > >>update tbl1 set t1_c4=999 where t1_id1 between 615000 and 615999; > --- 1000 row(s) updated. > >>update tbl1 set t1_c4=999 where t1_id1 between 820000 and 820999; > --- 1000 row(s) updated. > >> > >>create table tbl2( > +> t2_c1 integer unsigned > +> ,t2_c2 char(10) character set iso88591 > +> ,t2_c3 largeint > +> ,t2_c4 int > +> ,t2_c5 int > +> ,t2_id1 int not null > +> ,t2_id2 int not null) > +>store by (t2_id1) > +>salt using 8 partitions > +>; > --- SQL operation complete. > >>load into tbl2 > +>select c1+c2*10+c3*100+c4*1000+c5*10000 > +> ,cast(c1+c2*10+c3*100+c4*1000+c5*10000 as char(10) character set > iso88591) > +> ,c1+c2*10+c3*100+c4*1000+c5*10000 > +> ,c1+c2*10+c3*100+c4*1000+c5*10000 > +> ,c1 > +> ,c1+c2*10+c3*100+c4*1000+c5*10000 > +> ,c1+c2*10+c3*100+c4*1000+c5*10000 > +>from (values(1)) t > +>transpose 0,1,2,3,4,5,6,7,8,9 as c1 > +>transpose 0,1,2,3,4,5,6,7,8,9 as c2 > +>transpose 0,1,2,3,4,5,6,7,8,9 as c3 > +>transpose 0,1,2,3,4,5,6,7,8,9 as c4 > +>transpose 0,1,2,3,4,5,6,7,8,9 as c5 > +>; > Task: LOAD Status: Started Object: TRAFODION.ASCH.TBL2 > Task: CLEANUP Status: Started Object: TRAFODION.ASCH.TBL2 > Task: CLEANUP Status: Ended Object: TRAFODION.ASCH.TBL2 > Task: DISABLE INDEXE Status: Started Object: TRAFODION.ASCH.TBL2 > Task: DISABLE INDEXE Status: Ended Object: TRAFODION.ASCH.TBL2 > Task: PREPARATION Status: Started Object: TRAFODION.ASCH.TBL2 > Rows Processed: 100000 > Task: PREPARATION Status: Ended ET: 00:00:11.232 > Task: COMPLETION Status: Started Object: TRAFODION.ASCH.TBL2 > Task: COMPLETION Status: Ended ET: 00:00:01.051 > Task: POPULATE INDEX Status: Started Object: TRAFODION.ASCH.TBL2 > Task: POPULATE INDEX Status: Ended ET: 00:00:02.736 > --- 100000 row(s) loaded. > >>update statistics for table tbl2 on every column; > --- SQL operation complete. > >> > >>update statistics for table tbl1 on every column, > +>(t1_c1, t1_c2), (t1_c1, t1_c4), (t1_c2, t1_c3), > +>(t1_c3, t1_c4), (t1_c2, t1_c4) > +>; > --- SQL operation complete. > >> > >>prepare XX from select count(*) from tbl1, tbl2 where t1_c1 = t2_c1 and > >>t1_c2 = t2_c2; > --- SQL command prepared. > >> > >>explain options 'f' XX; > LC RC OP OPERATOR OPT DESCRIPTION CARD > ---- ---- ---- -------------------- -------- -------------------- --------- > 4 . 5 root 1.00E+000 > 3 . 4 sort_scalar_aggr 1.00E+000 > 2 1 3 hybrid_hash_join 2.50E+003 > . . 2 trafodion_scan TBL1 1.00E+002 > . . 1 trafodion_scan TBL2 1.00E+002 > --- SQL operation complete. > >>exit; > SECOND SESSION: > >>set schema asch; > --- SQL operation complete. > >>prepare XX from select count(*) from tbl1, tbl2 where t1_c1 = t2_c1 and > >>t1_c2 = t2_c2; > --- SQL command prepared. > >> > >>explain options 'f' XX; > LC RC OP OPERATOR OPT DESCRIPTION CARD > ---- ---- ---- -------------------- -------- -------------------- --------- > 8 . 9 root 1.00E+000 > 7 . 8 sort_partial_aggr_ro 1.00E+000 > 6 . 7 esp_exchange 1:12(hash2) 1.00E+000 > 5 . 6 sort_partial_aggr_le 1.00E+000 > 4 2 5 hybrid_hash_join 1.00E+005 > 3 . 4 esp_exchange 12(hash2):8(hash2) 1.00E+006 > . . 3 trafodion_scan TBL1 1.00E+006 > 1 . 2 esp_exchange 12(hash2):8(hash2) 1.00E+005 > . . 1 trafodion_scan TBL2 1.00E+005 > --- SQL operation complete. > >> > >>exit; > To reproduce: > step 1: initiate a sqlci session > drop schema asch cascade; > create schema asch; > set schema asch; > drop table tbl1; > drop table tbl2; > -- [a1] fact table; c1,c2,c3,c4 skewed > create table tbl1( > t1_c1 integer unsigned > ,t1_c2 char(10) character set iso88591 > ,t1_c3 largeint > ,t1_c4 int > ,t1_c5 int > ,t1_id1 int not null > ,t1_id2 int not null) > hash partition > store by (t1_id1) > salt using 8 partitions > ; > load into tbl1 > select c1+c2*10+c3*100+c4*1000+c5*10000+c6*100000 > ,cast(c1+c2*10+c3*100+c4*1000+c5*10000+c6*100000 as char(10) character > set iso88591) > ,c1+c2*10+c3*100+c4*1000+c5*10000+c6*100000 > ,c1+c2*10+c3*100+c4*1000+c5*10000+c6*100000 > ,c1 > ,c1+c2*10+c3*100+c4*1000+c5*10000+c6*100000 > ,c1+c2*10+c3*100+c4*1000+c5*10000+c6*100000 > from (values(1)) t > transpose 0,1,2,3,4,5,6,7,8,9 as c1 > transpose 0,1,2,3,4,5,6,7,8,9 as c2 > transpose 0,1,2,3,4,5,6,7,8,9 as c3 > transpose 0,1,2,3,4,5,6,7,8,9 as c4 > transpose 0,1,2,3,4,5,6,7,8,9 as c5 > transpose 0,1,2,3,4,5,6,7,8,9 as c6 > ; > update tbl1 set t1_c1=7777 where t1_id1 between 3000 and 6999; > update tbl1 set t1_c1=7777 where t1_id1 between 205000 and 206999; > update tbl1 set t1_c1=7777 where t1_id1 between 410000 and 411999; > update tbl1 set t1_c1=7777 where t1_id1 between 615000 and 615999; > update tbl1 set t1_c1=7777 where t1_id1 between 820000 and 820999; > update tbl1 set t1_c2='7777' where t1_id1 between 2500 and 6499; > update tbl1 set t1_c2='7777' where t1_id1 between 204000 and 205999; > update tbl1 set t1_c2='7777' where t1_id1 between 411000 and 412999; > update tbl1 set t1_c2='7777' where t1_id1 between 614500 and 615499; > update tbl1 set t1_c2='7777' where t1_id1 between 820500 and 821499; > update tbl1 set t1_c3=888 where t1_id1 between 2000 and 7999; > update tbl1 set t1_c3=888 where t1_id1 between 205000 and 205999; > update tbl1 set t1_c3=888 where t1_id1 between 410500 and 411499; > update tbl1 set t1_c3=888 where t1_id1 between 615500 and 616499; > update tbl1 set t1_c3=888 where t1_id1 between 820500 and 821499; > update tbl1 set t1_c4=999 where t1_id1 between 3000 and 6999; > update tbl1 set t1_c4=999 where t1_id1 between 205000 and 206999; > update tbl1 set t1_c4=999 where t1_id1 between 410000 and 411999; > update tbl1 set t1_c4=999 where t1_id1 between 615000 and 615999; > update tbl1 set t1_c4=999 where t1_id1 between 820000 and 820999; > create table tbl2( > t2_c1 integer unsigned > ,t2_c2 char(10) character set iso88591 > ,t2_c3 largeint > ,t2_c4 int > ,t2_c5 int > ,t2_id1 int not null > ,t2_id2 int not null) > store by (t2_id1) > salt using 8 partitions > ; > load into tbl2 > select c1+c2*10+c3*100+c4*1000+c5*10000 > ,cast(c1+c2*10+c3*100+c4*1000+c5*10000 as char(10) character set iso88591) > ,c1+c2*10+c3*100+c4*1000+c5*10000 > ,c1+c2*10+c3*100+c4*1000+c5*10000 > ,c1 > ,c1+c2*10+c3*100+c4*1000+c5*10000 > ,c1+c2*10+c3*100+c4*1000+c5*10000 > from (values(1)) t > transpose 0,1,2,3,4,5,6,7,8,9 as c1 > transpose 0,1,2,3,4,5,6,7,8,9 as c2 > transpose 0,1,2,3,4,5,6,7,8,9 as c3 > transpose 0,1,2,3,4,5,6,7,8,9 as c4 > transpose 0,1,2,3,4,5,6,7,8,9 as c5 > ; > update statistics for table tbl2 on every column; > update statistics for table tbl1 on every column, > (t1_c1, t1_c2), (t1_c1, t1_c4), (t1_c2, t1_c3), > (t1_c3, t1_c4), (t1_c2, t1_c4) > ; > prepare XX from select count(*) from tbl1, tbl2 where t1_c1 = t2_c1 and t1_c2 > = t2_c2; > explain options 'f' XX; > exit; > step 2: Initiate a new sqlci session and issue: > set schema asch; > prepare XX from select count(*) from tbl1, tbl2 where t1_c1 = t2_c1 and t1_c2 > = t2_c2; > explain options 'f' XX; > step 3: compare query plans generated in step 1 and 2. -- This message was sent by Atlassian JIRA (v6.3.4#6332)