Update does not pick index access
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
Trafodion |
New
|
High
|
Suresh Subbiah |
Bug Description
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.
-------
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... NOT NAMED
PLAN_ID .................. 212299508539703456
ROWS_OUT ................. 2
EST_TOTAL_COST ........... 0
STATEMENT ................ UPDATE seabase.
-------
ROOT =======
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_
total_
upd_action_
xn_access_mode ......... read_only
xn_autoabort_
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 =======
TABLE_NAME ............... TRAFODION.
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.
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
--- SQL operation complete.
Qifan provided a shape that provides relief
control query shape nested_
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.