2

I have a large table containing over 15million records with 4 columns that form a compound key. Because we use a development environment that doesn't support compound keys I was required to create a view on that table to add a new column that would be the concatenation of all 4 compound keys. Then because the view had to be updateable, I created 3 instead of triggers on that view so that users can query the view providing the concatenated compound key column instead of the individual columns. Although the triggers work, the update trigger is very very slow despite the fact that its using all 4 compound keys in the where clause. Out of curiosity I created an anonymous block and hardcoded some values to see if the performance would be any better and the update executed in under 1 sec! Any ideas why the same(?) update query in the trigger takes 5 mins to execute?

Below is the trigger code and further down folows the anonymous block. Thanks for your help!

CREATE OR REPLACE TRIGGER v_cust_order_item_update
INSTEAD OF UPDATE ON v_cust_order_item
REFERENCING NEW AS n                

DECLARE
   v_fk3_arzo_code cust_order_item.fk3_arzo_code%TYPE;
   v_fk3_cuor_number cust_order_item.fk3_cuor_number%TYPE;
   v_fk3_cuor_reg_year cust_order_item.fk3_cuor_reg_year%TYPE;
   v_line_number cust_order_item.line_number%TYPE DEFAULT 0;
   v_position_1 NUMBER;
   v_position_2 NUMBER;
   v_position_3 NUMBER;
   e_malformed_pk EXCEPTION;
BEGIN

-- parse the compound key
v_position_1 := instr(:n.cust_order_item_pk,'/');

if v_position_1 = 0 then
  raise  e_malformed_pk;
end if;

v_fk3_arzo_code := substr(:n.cust_order_item_pk,1,(v_position_1 - 1));

v_position_2 := instr(:n.cust_order_item_pk,'/', (v_position_1 + 1));

if v_position_2 = 0 then
  raise e_malformed_pk;
end if;

v_fk3_cuor_number := to_number(substr(:n.cust_order_item_pk,(v_position_1 + 1),(v_position_2 - v_position_1 - 1)));

v_position_3 := instr(:n.cust_order_item_pk,'/', (v_position_2 + 1));

v_fk3_cuor_reg_year := to_number(substr(:n.cust_order_item_pk, (v_position_2 + 1),(v_position_3 - v_position_2 - 1)));

-- the year should be 4 characters long
if length(to_char(v_fk3_cuor_reg_year)) <> 4 then
  raise e_malformed_pk;
end if;

v_line_number := to_number(substr(:n.cust_order_item_pk, (v_position_3 + 1), length(:n.cust_order_item_pk) - v_position_3));

if v_line_number = 0 then
                raise e_malformed_pk;
end if;

-- Update the table using the parsed key values
UPDATE cust_order_item 
SET 
fax_indicator = :n.fax_indicator,
required_date_flag = :n.required_date_flag,
type = :n.type, 
quantity = :n.quantity,
registration_date = :n.registration_date,
required_date = :n.required_date,
customer_comment = :n.customer_comment,
status_date = :n.status_date, 
cancellation_origi = :n.cancellation_origi, 
cancellation_reaso = :n.cancellation_reaso, 
cyta_approval_stat = :n.cyta_approval_stat, 
manual_allocation = :n.manual_allocation, 
excessive_material = :n.excessive_material, 
survey_status = :n.survey_status, 
survey_status_date = :n.survey_status_date, 
wayleave_status = :n.wayleave_status,
wayleave_status_da = :n.wayleave_status_da, 
ex_directory_indic = :n.ex_directory_indic, 
customer_reference = :n.customer_reference, 
cyta_approval_orig = :n.cyta_approval_orig,
d_entry_user_id = :n.d_entry_user_id,
clearance_date = :n.clearance_date,
feasibility_date = :n.feasibility_date,
surveyor_reference = :n.surveyor_reference,
wayleave_trace = :n.wayleave_trace,
d_manual_alloc_aut = :n.d_manual_alloc_aut,
d_last_examined_us = :n.d_last_examined_us,
cyta_approval_sta0 = :n.cyta_approval_sta0,
introduced_by_cyta = :n.introduced_by_cyta,
d_req_submission_p = :n.d_req_submission_p,
date_last_update = :n.date_last_update,
last_upd_user_id = :n.last_upd_user_id, 
status = :n.status,
customer_notif_sta = :n.customer_notif_sta,
customer_notificat = :n.customer_notificat,
dwelling_type = :n.dwelling_type,
directory_address = :n.directory_address,
fk1_addr_serial = :n.fk1_addr_serial,
fk2_addr_serial = :n.fk2_addr_serial, 
fk4_pcat_code = :n.fk4_pcat_code,
fk4_ccir_number = :n.fk4_ccir_number,
fk5_cuor_reg_year = :n.fk5_cuor_reg_year,
fk5_cuor_number :n.fk5_cuor_number, 
fk5_arzo_code = :n.fk5_arzo_code, 
fk5_cuoi_line_num = :n.fk5_cuoi_line_num,
fk6_pcat_code = :n.fk6_pcat_code,
fk6_prod_ser_num = :n.fk6_prod_ser_num,
fk7_fleq_number = :n.fk7_fleq_number,
fk7_flty_code = :n.fk7_flty_code, 
fk7_scon_con_num = :n.fk7_scon_con_num, 
fk8_atet_serv_cat = :n.fk8_atet_serv_cat,
fk8_atet_ser_num = :n.fk8_atet_ser_num, 
fk8_teeq_ser_num = :n.fk8_teeq_ser_num, 
completion_referen = :n.completion_referen, 
data_lines_check_f = :n.data_lines_check_f,
tie_allocated_flag = :n.tie_allocated_flag,
isdn_relative_work = :n.isdn_relative_work,
gsm_imsi_change_re = :n.gsm_imsi_change_re, 
detailed_dir_spec = :n.detailed_dir_spec, 
gsm_voice_mail_lan = :n.gsm_voice_mail_lan, 
gsm_card_type = :n.gsm_card_type,
toll_ticketing_fla = :n.toll_ticketing_fla, 
paging_no = :n.paging_no,
paging_type = :n.paging_type,
cgrp_d_code = :n.cgrp_d_code,
d_dir_name_ser_num = :n.d_dir_name_ser_num,
comment_flag = :n.comment_flag,
info_txt1 = :n.info_txt1,
info_txt2 = :n.info_txt2,
info_txt3 = :n.info_txt3,
info_txt4 = :n.info_txt4, 
info_txt5 = :n.info_txt5, 
info_txt6 = :n.info_txt6,
info_num1 = :n.info_num1, 
info_num2 = :n.info_num2, 
info_num3 = :n.info_num3,
registration_tmstp = :n.registration_tmstp,
main_cct = :n.main_cct, 
main_cct_pcat = :n.main_cct_pcat,
fk_cdfl_ser_num = :n.fk_cdfl_ser_num, 
related_circuit_no = :n.related_circuit_no,
dsl_tech_priority = :n.dsl_tech_priority,
access_technology = :n.access_technology
WHERE fk3_arzo_code = v_fk3_arzo_code AND
 fk3_cuor_number = v_fk3_cuor_number AND 
 fk3_cuor_reg_year = v_fk3_cuor_reg_year AND
 line_number = v_line_number;

EXCEPTION
WHEN e_malformed_pk then
  raise_application_error(-20000, 'Malformed primary key in v_cust_order_item_update.');
WHEN OTHERS THEN
  raise_application_error(-20001, 'Unhandled exception in v_cust_order_item_update.' || SQLCODE);
END;



DECLARE
   v_fk3_arzo_code cust_order_item.fk3_arzo_code%TYPE;
   v_fk3_cuor_number cust_order_item.fk3_cuor_number%TYPE;
   v_fk3_cuor_reg_year cust_order_item.fk3_cuor_reg_year%TYPE;
   v_line_number cust_order_item.line_number%TYPE DEFAULT 0;
   v_position_1 NUMBER;
   v_position_2 NUMBER;
   v_position_3 NUMBER;
   e_malformed_pk EXCEPTION;
   c_test VARCHAR2(100);
BEGIN

dbms_output.put_line('BEFORE PARSE:' || SYSDATE );

-- Hardcode a concatenated compound key c_test := 'NC/99999/2015/1';

-- parse the compound key
v_position_1 := instr(c_test,'/');

if v_position_1 = 0 then
  raise  e_malformed_pk;
end if;

v_fk3_arzo_code := substr(c_test,1,(v_position_1 - 1));

v_position_2 := instr(c_test,'/', (v_position_1 + 1));

if v_position_2 = 0 then
  raise e_malformed_pk;
end if;

v_fk3_cuor_number := to_number(substr(c_test,(v_position_1 + 1),(v_position_2 - v_position_1 - 1)));

v_position_3 := instr(c_test,'/', (v_position_2 + 1));

v_fk3_cuor_reg_year := to_number(substr(c_test, (v_position_2 + 1),(v_position_3 - v_position_2 - 1)));

-- the reamining characters if there are any will be the year
if length(to_char(v_fk3_cuor_reg_year)) <> 4 then
  raise e_malformed_pk;
end if;

v_line_number := to_number(substr(c_test, (v_position_3 + 1), length(c_test) - v_position_3));

if v_line_number = 0 then
  raise e_malformed_pk;
end if;

dbms_output.put_line('BEFORE UPDATE:' || SYSDATE );

UPDATE cust_order_item 
SET 
fax_indicator = 'N',
required_date_flag = 'O',
type = 'S', 
quantity = 1,
registration_date = '01/01/2015',
required_date = '01/01/2015',
customer_comment = 'COMMENT',
status_date = '01/01/2015', 
cancellation_origi = 'CYTA', 
cancellation_reaso = 'TEST', 
cyta_approval_stat = '01/01/2015', 
manual_allocation = 'N', 
excessive_material = 0, 
survey_status = 'COMPLETED', 
survey_status_date = '01/01/2015', 
wayleave_status = 'TEST',
wayleave_status_da = '01/01/2015', 
ex_directory_indic = 'N', 
customer_reference = 'AAE 66', 
cyta_approval_orig = 'X',
d_entry_user_id = 'U9983$',
clearance_date = '01/01/2015',
feasibility_date = '01/01/2015',
surveyor_reference = 'TEST',
wayleave_trace = 'CYTA',
d_manual_alloc_aut = 'X',
d_last_examined_us = 'U9983$',
cyta_approval_sta0 = 'X',
introduced_by_cyta = 'N',
d_req_submission_p = 'CT',
date_last_update = '01/01/2015',
last_upd_user_id = 'EURO1', 
status = 'COMPLETED',
customer_notif_sta = '01/01/2015',
customer_notificat = 'PRO',
dwelling_type = 'R',
directory_address = 'A',
fk1_addr_serial = 858484,
fk2_addr_serial = 3401529, 
fk4_pcat_code = 'TF',
fk4_ccir_number = 23731033,
fk5_cuor_reg_year = 2015,
fk5_cuor_number = 9999, 
fk5_arzo_code = 'NC', 
fk5_cuoi_line_num = 1,
fk6_pcat_code = 'TF',
fk6_prod_ser_num = 1,
fk7_fleq_number = 76253,
fk7_flty_code = 4, 
fk7_scon_con_num = 2, 
fk8_atet_serv_cat = 'X',
fk8_atet_ser_num = 1, 
fk8_teeq_ser_num = 1, 
completion_referen = 'X', 
data_lines_check_f = 'X',
tie_allocated_flag = 'X',
isdn_relative_work = 0,
gsm_imsi_change_re = 'XX', 
detailed_dir_spec = 'X', 
gsm_voice_mail_lan = 'X', 
gsm_card_type = 'X',
toll_ticketing_fla = 'X', 
paging_no = 1,
paging_type = 1,
cgrp_d_code = 1,
d_dir_name_ser_num = 1,
comment_flag = 'X',
info_txt1 = 'TEST',
info_txt2 = 'TEST',
info_txt3 = 'TEST',
info_txt4 = 'TEST', 
info_txt5 = 'TEST', 
info_txt6 = 'TEST',
info_num1 = 1, 
info_num2 = 1, 
info_num3 = 1,
registration_tmstp = '01/01/2015',
main_cct = 1, 
main_cct_pcat = 'XX',
fk_cdfl_ser_num = 1, 
related_circuit_no = 1,
dsl_tech_priority = 'TEST',
access_technology = 'TEST'
WHERE fk3_arzo_code = v_fk3_arzo_code AND
fk3_cuor_number = v_fk3_cuor_number AND 
 fk3_cuor_reg_year = v_fk3_cuor_reg_year AND
line_number = v_line_number;



dbms_output.put_line('AFTER UPDATE:' || SYSDATE );
dbms_output.put_line('v_fk3_arzo_code:' || v_fk3_arzo_code );
dbms_output.put_line('v_fk3_cuor_number:' || v_fk3_cuor_number );
dbms_output.put_line('v_fk3_cuor_reg_year:' || v_fk3_cuor_reg_year );
dbms_output.put_line('v_line_number:' || v_line_number );

EXCEPTION
WHEN e_malformed_pk then
  raise_application_error(-20000, 'Malformed primary key in v_cust_order_item_update.');
WHEN OTHERS THEN
  raise_application_error(-20001, 'Unhandled exception in v_cust_order_item_update.' || SQLCODE || SQLERRM);
END;
5
  • 1
    Where is your for each row? Do you really want a statement level trigger or do you want a row level trigger? Jan 19, 2015 at 13:12
  • Initially I had it but I removed it as the trigger should update a single record only. It got slightly faster after I removed it.
    – Dimitris
    Jan 20, 2015 at 5:46
  • 1
    Many possibilities, 1. Do you have a proper index on those keys on that table? 2. Which version of Oracle do you use? If you are using 11gr2 or later, can you use a virtual column to work-around this (possibly)? 3. this should be a FOR EACH ROW trigger. 5. Run a sql trace when you update one row. Analyze that trace file and it will tell you where it is slowing down.
    – Raj
    Jan 20, 2015 at 17:41
  • I understood what the problem is. The trigger code is fine. The problem lies with the Update statement I issue that triggers the trigger. Because my statement is something like UPDATE v_cust_order_item SET acolumn = avalue WHERE cust_order_item_pk = 'NC/99999/2015/1', this statement does not use the composite key but the concatenated key which is of course not indexed! I need somehow to create an index on the view on the computed cust_order_item_pk. But indexes on views are not possible as far as I know. We use Oracle 11gr2.
    – Dimitris
    Jan 21, 2015 at 7:30
  • Correct, indeses cannot be created on views. Only on MViews you can create an index.
    – user56901
    Jan 21, 2015 at 17:25

1 Answer 1

2

I'm not sure you need instead of triggers here.

If you are on Oracle 11g release 1 or later, create a virtual column for your concatenated compound key in the original table. Then create a unique index on the virtual column. You ought to be able to use this virtual column as a unique key.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.