Details
-
Bug
-
Status: Closed
-
Major
-
Resolution: Fixed
-
2.2.0
-
None
-
None
Description
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_idx_b on tbl(b) salt like table;
update statistics for table tbl on every column sample;
prepare s from select k1 where b = '1234567';
prepare ss from select k1 from b like '1234567%';
see how s is correctly picking index access.
see how ss, regardless of th elike correctly been transform into a range spec, end up doing a full main table scan instead of going after the index on b using MDAM and the range spec inside the mdam disjunct.
SQL>prepare s from select k1 from tbl where b = '1234567';
— SQL command prepared.
SQL>explain options 'f' s;
LC RC OP OPERATOR OPT DESCRIPTION CARD
---- ---- ---- -------------------- -------- -------------------- ---------
1 . 2 root 1.00E+000
. . 1 trafodion_index_scan IDX_TBL_B 1.00E+000
— SQL operation complete.
SQL>explain s;
------------------------------------------------------------------ PLAN SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... S
PLAN_ID .................. 212355075543213868
ROWS_OUT ................. 1
EST_TOTAL_COST ........... 0.15
STATEMENT ................ select k1 from tbl where b = '1234567'
------------------------------------------------------------------ NODE LISTING
ROOT ====================================== SEQ_NO 2 ONLY CHILD 1
REQUESTS_IN .............. 1
ROWS_OUT ................. 1
EST_OPER_COST ............ 0
EST_TOTAL_COST ........... 0.15
DESCRIPTION
max_card_est ........... 1
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
statement_index ........ 0
affinity_value ......... 0
max_max_cardinality .... 1
total_overflow_size .... 0.00 KB
xn_access_mode ......... read_only
xn_autoabort_interval 0
auto_query_retry ....... enabled
plan_version ....... 2,600
embedded_arkcmp ........ used
ObjectUIDs ............. 636255280475776270
select_list ............ TRAFODION.ERIC.IDX_TBL_B.K1
input_variables ........ %('1234567')
TRAFODION_INDEX_SCAN ====================== SEQ_NO 1 NO CHILDREN
TABLE_NAME ............... TBL
REQUESTS_IN .............. 1
ROWS_OUT ................. 1
EST_OPER_COST ............ 0.15
EST_TOTAL_COST ........... 0.15
DESCRIPTION
max_card_est ........... 1
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
scan_type .............. subset scan limited by mdam of index
TRAFODION.ERIC.IDX_TBL_B(TRAFODION.ERIC.TBL)
object_type ............ Trafodion
cache_size ........... 100
probes ................. 1
rows_accessed .......... 1
column_retrieved ....... #1:1
key_columns ............ TRAFODION.ERIC.IDX_TBL_B.SALT,
TRAFODION.ERIC.IDX_TBL_B.B,
TRAFODION.ERIC.IDX_TBL_B.DIVISION_1,
TRAFODION.ERIC.IDX_TBL_B.K1,
TRAFODION.ERIC.IDX_TBL_B.K2,
TRAFODION.ERIC.IDX_TBL_B.TS
mdam_disjunct .......... (TRAFODION.ERIC.IDX_TBL_B.B = %('1234567'))
— SQL operation complete.
SQL>prepare ss from select k1 from tbl where b like '1234567%';
— SQL command prepared.
SQL>explain options 'f' ss;
LC RC OP OPERATOR OPT DESCRIPTION CARD
---- ---- ---- -------------------- -------- -------------------- ---------
1 . 2 root 6.25E+006
. . 1 trafodion_index_scan IDX_TBL_B 6.25E+006
— SQL operation complete.
SQL>explain ss;
------------------------------------------------------------------ PLAN SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... SS
PLAN_ID .................. 212355075594072438
ROWS_OUT ......... 6,253,401
EST_TOTAL_COST ......... 182.04
STATEMENT ................ select k1 from tbl where b like '1234567%'
------------------------------------------------------------------ NODE LISTING
ROOT ====================================== SEQ_NO 2 ONLY CHILD 1
REQUESTS_IN .............. 1
ROWS_OUT ......... 6,253,401
EST_OPER_COST ............ 0
EST_TOTAL_COST ......... 182.04
DESCRIPTION
max_card_est ........... 1.00054e+08
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
statement_index ........ 0
affinity_value ......... 0
max_max_cardinal 6,253,401
total_overflow_size .... 0.00 KB
xn_access_mode ......... read_only
xn_autoabort_interval 0
auto_query_retry ....... enabled
plan_version ....... 2,600
embedded_arkcmp ........ used
ObjectUIDs ............. 636255280475776270
select_list ............ TRAFODION.ERIC.IDX_TBL_B.K1
TRAFODION_INDEX_SCAN ====================== SEQ_NO 1 NO CHILDREN
TABLE_NAME ............... TBL
REQUESTS_IN .............. 1
ROWS_OUT ......... 6,253,401
EST_OPER_COST .......... 182.04
EST_TOTAL_COST ......... 182.04
DESCRIPTION
max_card_est ........... 1.00054e+08
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
scan_type .............. subset scan of index TRAFODION.ERIC.IDX_TBL_B(TRAFOD
ION.ERIC.TBL)
object_type ............ Trafodion
columns ................ all
begin_keys(incl)
end_keys(incl)
cache_size ........ 10,000
probes ................. 1
rows_accessed .......... 1.00054e+08
column_retrieved ....... #1:1
key_columns ............ TRAFODION.ERIC.IDX_TBL_B.SALT,
TRAFODION.ERIC.IDX_TBL_B.B,
TRAFODION.ERIC.IDX_TBL_B.DIVISION_1,
TRAFODION.ERIC.IDX_TBL_B.K1,
TRAFODION.ERIC.IDX_TBL_B.K2,
TRAFODION.ERIC.IDX_TBL_B.TS
executor_predicates .... (TRAFODION.ERIC.IDX_TBL_B.B >= '1234567') and
(TRAFODION.ERIC.IDX_TBL_B.B < '1234568')
— SQL operation complete.