You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@trafodion.apache.org by "David Wayne Birdsall (JIRA)" <ji...@apache.org> on 2016/05/12 16:03:13 UTC
[jira] [Updated] (TRAFODION-1253) LP Bug: 1460727 - Update does not
pick index access
[ https://issues.apache.org/jira/browse/TRAFODION-1253?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
David Wayne Birdsall updated TRAFODION-1253:
--------------------------------------------
Fix Version/s: (was: 2.0-incubating)
2.1-incubating
> LP Bug: 1460727 - Update does not pick index access
> ---------------------------------------------------
>
> Key: TRAFODION-1253
> URL: https://issues.apache.org/jira/browse/TRAFODION-1253
> Project: Apache Trafodion
> Issue Type: Bug
> Components: sql-cmp
> Reporter: Suresh Subbiah
> Assignee: David Wayne Birdsall
> Priority: Critical
> Fix For: 2.1-incubating
>
>
> CREATE TABLE ACCT_BALANCE
> (
> ACCT_BALANCE_ID INT NO DEFAULT NOT NULL NOT DROPPABLE
> , ACCT_BALANCE_NAME VARCHAR(250 CHARS) CHARACTER SET UTF8
> COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
> , CUST_ID INT NO DEFAULT NOT NULL NOT DROPPABLE
> , BALANCE INT NO DEFAULT NOT NULL NOT DROPPABLE
> , BALANCE_TYPE_ID INT NO DEFAULT NOT NULL NOT DROPPABLE
> , STATE VARCHAR(6 CHARS) CHARACTER SET UTF8
> COLLATE DEFAULT DEFAULT NULL
> , WRITE_OFF INT NO DEFAULT NOT NULL NOT DROPPABLE
> , PRIMARY KEY (ACCT_BALANCE_ID ASC)
> )
> SALT USING 120 PARTITIONS
> ;
>
> CREATE UNIQUE INDEX ACCT_CUST_I ON ACCT_BALANCE
> (
> CUST_ID ASC
> )
> ;
> UPDATE ACCT_BALANCE SET BALANCE = BALANCE+1 WHERE CUST_ID = 103 AND STATE = '1000';
> gets a plan that does not use the index and therefore takes too long.
> >>explain UPDATE seabase.ACCT_BALANCE SET BALANCE = BALANCE+1 WHERE CUST_ID = 103 AND STATE = '1000';
>
> ------------------------------------------------------------------ PLAN SUMMARY
> MODULE_NAME .............. DYNAMICALLY COMPILED
> STATEMENT_NAME ........... NOT NAMED
> PLAN_ID .................. 212299508539703456
> ROWS_OUT ................. 2
> EST_TOTAL_COST ........... 0
> STATEMENT ................ UPDATE seabase.ACCT_BALANCE
> SET BALANCE = BALANCE+1
> WHERE CUST_ID = 103 AND STATE = '1000';
>
>
> ------------------------------------------------------------------ NODE LISTING
> ROOT ====================================== SEQ_NO 2 ONLY CHILD 1
> REQUESTS_IN .............. 1
> ROWS_OUT ................. 2
> EST_OPER_COST ............ 0
> EST_TOTAL_COST ........... 0
> DESCRIPTION
> olt_optimization ....... used
> max_card_est ........... 2
> fragment_id ............ 0
> parent_frag ............ (none)
> fragment_type .......... master
> statement_index ........ 0
> affinity_value ......... 0
> max_max_cardinality .... 2
> total_overflow_size .... 0.00 KB
> upd_action_on_error .... xn_rollback
> xn_access_mode ......... read_only
> xn_autoabort_interval 0
> auto_query_retry ....... enabled
> plan_version ....... 2,600
> embedded_arkcmp ........ used
> IS_SQLCI ............... ON
> LDAP_USERNAME
> SCHEMA ................. SEABASE
> GENERATE_EXPLAIN ....... ON
> ObjectUIDs ............. 183026107640624619
> input_variables ........ %(1), %(103), %('1000')
>
>
> TRAFODION_UPDATE ========================== SEQ_NO 1 NO CHILDREN
> TABLE_NAME ............... TRAFODION.SEABASE.ACCT_BALANCE
> REQUESTS_IN .............. 1
> ROWS_OUT ................. 2
> EST_OPER_COST ............ 0
> EST_TOTAL_COST ........... 0
> DESCRIPTION
> max_card_est ........... 2
> fragment_id ............ 0
> parent_frag ............ (none)
> fragment_type .......... master
> iud_type ............... trafodion_update TRAFODION.SEABASE.ACCT_BALANCE
> columns ................ all
> begin_keys(incl)
> end_keys(incl)
> new_rec_expr ........... (BALANCE assign (cast(BALANCE) + cast(%(1))))
> predicate .............. (STATE = %('1000')) and (CUST_ID = %(103)) and
> (CUST_ID = %(103))
>
> --- SQL operation complete.
> Qifan provided a shape that provides relief
> control query shape nested_join(nested_join(cut,cut),cut);
> Adding the index column to the tail of the base table's key also seems to help.
> Debugging shows that costmethod is needed for HBaseUpdate operator. same problem likely exists for Delete too.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)