You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@trafodion.apache.org by "Alice Chen (JIRA)" <ji...@apache.org> on 2015/07/22 20:20:34 UTC
[jira] [Created] (TRAFODION-1253) LP Bug: 1460727 - Update does not
pick index access
Alice Chen created TRAFODION-1253:
-------------------------------------
Summary: 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: Suresh Subbiah
Priority: Critical
Fix For: 2.0-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)