You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@trafodion.apache.org by "Alice Chen (JIRA)" <ji...@apache.org> on 2015/07/22 20:15:19 UTC
[jira] [Created] (TRAFODION-237) LP Bug: 1311871 - compiler doesn't
set begin and end key for salted tables
Alice Chen created TRAFODION-237:
------------------------------------
Summary: 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
Assignee: Ravisha Neelakanthappa
Priority: Critical
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)