Update does not pick index access

[Suresh Subbiah]

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.