[ https://issues.apache.org/jira/browse/TRAFODION-237?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Atanu Mishra closed TRAFODION-237. ---------------------------------- Resolution: Fixed Assignee: (was: Ravisha Neelakanthappa) Fix Version/s: 0.8 (pre-incubation) > LP Bug: 1311871 - compiler doesn't set begin and end key for salted tables > -------------------------------------------------------------------------- > > Key: TRAFODION-237 > URL: https://issues.apache.org/jira/browse/TRAFODION-237 > Project: Apache Trafodion > Issue Type: Bug > Components: sql-cmp > Reporter: Ravisha Neelakanthappa > Priority: Critical > Fix For: 0.8 (pre-incubation) > > > The compiler doesn't set begin and end key on salted table. Because of this, > scan on Habase tables with range predicates on key columns reads all the > rows. Same problem exist for both serial plan (with single partition > function) and parallel plan with range partition function. > To reproduce : > set schema trafodion.hbase; > drop table shb2; > create table shb2 > (uniq int not null, > c10K int , > c1K int, > c100 int, > c10 int, > c1 int, > primary key (uniq) > ) salt using 4 partitions ; > upsert with no rollback > into shb2 > select > 0 + (10000 * x10000) + (1000 * x1000) + (100 * x100) + (10 * x10) +( 1 * x1), > 0 + (1000 * x1000) + (100 * x100) + (10 * x10) + (1 * x1), > 0 + (100 * x100) + (10 * x10) + (1 * x1), > 0 + (10 * x10) + (1 * x1), > 0 + (1 * x1), > 0 > from (values(0))t > 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 x1000 > 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 x10 > transpose 0,1,2,3,4,5,6,7,8,9 as x1 > ; > update statistics for table shb2 on every column; > prepare xx from > select count(*), uniq, c10K, c1K, c100, c10, c1 > from shb1 > where shb1.uniq < 50 > group by uniq, c10K, c1K, C100, c10, c1 > for read uncommitted access; > >>explain options 'f' xx; > LC RC OP OPERATOR OPT DESCRIPTION CARD > ---- ---- ---- -------------------- -------- -------------------- --------- > 3 . 4 root 1.00E+000 > 2 . 3 firstn 1.00E+000 > 1 . 2 hash_groupby 5.00E+001 > . . 1 trafodion_scan SHB1 5.00E+001 > --- SQL operation complete. > TRAFODION_SCAN ============================ SEQ_NO 1 NO CHILDREN > TABLE_NAME ............... SHB1 > REQUESTS_IN .............. 1 > ROWS_OUT ................ 50 > EST_OPER_COST ............ 0.03 > EST_TOTAL_COST ........... 0.03 > DESCRIPTION > max_card_est .......... 50 > fragment_id ............ 0 > parent_frag ............ (none) > fragment_type .......... master > scan_type .............. subset scan of table TRAFODION.HBASE.SHB1 > columns ................ all > begin_keys(incl) > end_keys(incl) > key_columns ............ _SALT_, UNIQ > executor_predicates .... (UNIQ < 50) > part_key_predicates .... (UNIQ < 50) > >> execute xx; > --- 50 row(s) selected. > Start Time 2014/04/16 15:32:38.308978 > End Time 2014/04/16 15:32:45.236699 > Elapsed Time 00:00:06.927721 > Compile Time 00:00:00.093071 > Execution Time 00:00:06.927721 > Table Name Records Records Hdfs Hdfs I/O > Hdfs Access > Accessed Used I/Os Bytes > Time(usec) > TRAFODION.HBASE.SHB1 > 100000 50 0 0 > 6923951 > --- SQL operation complete. > -- with Range partition plan > >>explain options 'f' xx; > LC RC OP OPERATOR OPT DESCRIPTION CARD > ---- ---- ---- -------------------- -------- -------------------- --------- > 4 . 5 root 5.00E+001 > 3 . 4 esp_exchange 1:12(hash2) 5.00E+001 > 2 . 3 hash_groupby 5.00E+001 > 1 . 2 esp_exchange 12(hash2):2(range) 5.00E+001 > . . 1 trafodion_scan SHB1 5.00E+001 > --- SQL operation complete. > TRAFODION_SCAN ============================ SEQ_NO 1 NO CHILDREN > TABLE_NAME ............... SHB1 > REQUESTS_IN .............. 1 > ROWS_OUT ................ 50 > EST_OPER_COST ............ 0.03 > EST_TOTAL_COST ........... 0.03 > DESCRIPTION > max_card_est .......... 50 > fragment_id ............ 3 > parent_frag ............ 2 > fragment_type .......... esp > scan_type .............. subset scan of table TRAFODION.HBASE.SHB1 > key_columns ............ _SALT_, UNIQ > executor_predicates .... (UNIQ < 50) > part_key_predicates .... (UNIQ < 50) > begin_key .............. (_SALT_ = \:_sys_HostVarLo0), > (UNIQ = \:_sys_HostVarLo1) > end_key ................ (_SALT_ = \:_sys_HostVarHi0), > (UNIQ = \:_sys_HostVarHi1) > --- SQL operation complete. > >> > >> execute xx; > --- 50 row(s) selected. > Start Time 2014/04/23 20:32:37.949880 > End Time 2014/04/23 20:32:42.787625 > Elapsed Time 00:00:04.837745 > Compile Time 00:00:00.112411 > Execution Time 00:00:04.837745 > Table Name Records Records Hdfs Hdfs I/O > Hdfs Access > Accessed Used I/Os Bytes > Time(usec) > TRAFODION.HBASE.SHB1 > 100000 50 0 0 > 4485532 > --- SQL operation complete. -- This message was sent by Atlassian JIRA (v6.3.4#6332)