[ https://issues.apache.org/jira/browse/TRAFODION-2512?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Suresh Subbiah reassigned TRAFODION-2512: ----------------------------------------- Assignee: Suresh Subbiah > index access with MDAM not chosen where predicate is range spec > --------------------------------------------------------------- > > Key: TRAFODION-2512 > URL: https://issues.apache.org/jira/browse/TRAFODION-2512 > Project: Apache Trafodion > Issue Type: Bug > Components: sql-cmp > Affects Versions: 2.2-incubating > Reporter: Eric Owhadi > Assignee: Suresh Subbiah > > 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. -- This message was sent by Atlassian JIRA (v6.3.15#6346)