You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@trafodion.apache.org by "Suresh Subbiah (JIRA)" <ji...@apache.org> on 2017/04/08 04:11:41 UTC
[jira] [Commented] (TRAFODION-2512) index access with MDAM not
chosen where predicate is range spec
[ https://issues.apache.org/jira/browse/TRAFODION-2512?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15961672#comment-15961672 ]
Suresh Subbiah commented on TRAFODION-2512:
-------------------------------------------
This is the plan we get with a fix. The issue was with like predicate where the prefix pattern has 7 characters or more. Table below had only a million rows instead of 100 million as shown above.
>>explain ss ;
------------------------------------------------------------------ PLAN SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... SS
PLAN_ID .................. 212358384424674474
ROWS_OUT ................. 1
EST_TOTAL_COST ........... 0.03
STATEMENT ................ select k1 from tbl where b like '1234567%';
------------------------------------------------------------------ NODE LISTING
ROOT ====================================== SEQ_NO 2 ONLY CHILD 1
REQUESTS_IN .............. 1
ROWS_OUT ................. 1
EST_OPER_COST ............ 0
EST_TOTAL_COST ........... 0.03
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 ............. 5982595032094170298
select_list ............ TRAFODION.SEABASE.TBL_IDX1_B.K1
TRAFODION_INDEX_SCAN ====================== SEQ_NO 1 NO CHILDREN
TABLE_NAME ............... TBL
REQUESTS_IN .............. 1
ROWS_OUT ................. 1
EST_OPER_COST ............ 0.03
EST_TOTAL_COST ........... 0.03
DESCRIPTION
max_card_est ........... 1
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
scan_type .............. subset scan limited by mdam of index
TRAFODION.SEABASE.TBL_IDX1_B(TRAFODION.SEABASE.TBL
)
object_type ............ Trafodion
cache_size ........... 100
probes ................. 1
rows_accessed .......... 1
column_retrieved ....... #1:1
key_columns ............ TRAFODION.SEABASE.TBL_IDX1_B._SALT_,
TRAFODION.SEABASE.TBL_IDX1_B.B,
TRAFODION.SEABASE.TBL_IDX1_B._DIVISION_1_,
TRAFODION.SEABASE.TBL_IDX1_B.K1,
TRAFODION.SEABASE.TBL_IDX1_B.K2,
TRAFODION.SEABASE.TBL_IDX1_B.TS
mdam_disjunct .......... (TRAFODION.SEABASE.TBL_IDX1_B.B >= '1234567') and
(TRAFODION.SEABASE.TBL_IDX1_B.B < '1234568')
--- SQL operation complete.
>>
> 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)