[
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)
