compound trigger - performance impact

Question

I want to ask question on performance of compound trigger, I have actually implemented it recently and observed great performance improvement also. But there is something which I want to discuss.

Please consider the below scenario for my question :

Suppose there are two tables created as below:

~ Two tables are created :

: CREATE TABLE ORDERS (ORD_ID NUMBER, ITEM_CODE VARCHAR2(100), ORD_QTY NUMBER,ORD_DATE DATE);

: CREATE TABLE ORDER_ARCHIVE(ORD_ID NUMBER, ORD_CODE VARCHAR2(100));

~ Now I have created compound trigger as given below :

create or replace trigger TRG_COMP_SAL 
for update or insert on ORDERS 
compound trigger --Trigger Type is Compound here 
type t_tbl_typ is table of ORDER_ARCHIVE%rowtype index by pls_integer; 
v_tbl_events t_tbl_typ; 
idx pls_integer := 0; 
--After Each row statement 
after each row is 
begin 
IF INSERTING THEN 
idx := idx + 1; 
v_tbl_events(idx).ORD_ID := :new.ORD_ID; 
v_tbl_events(idx).ORD_CODE := :NEW.ITEM_CODE; 
END IF; 
-- Checking threshold limit for indx which will be used for bulk insert 
if idx >= 100 then 
forall cnt in 1 .. v_tbl_events.count() 
insert into ORDER_ARCHIVE values v_tbl_events (cnt); 
v_tbl_events.delete; 
idx := 0;-- resetting threshold limit for indx which will be used for bulk insert 
end if; 
end after each row; 
--After statement 
after statement is 
begin 
IF INSERTING THEN 
--Using forall to bulk insert data 
forall cnt in 1 .. v_tbl_events.count() 
insert into ORDER_ARCHIVE values v_tbl_events (cnt); 
END IF; 
end after statement; 
end TRG_COMP_SAL;

This is surely faster in execution as compared with the normal trigger. But, I am little confused about :

In case of bulk insert, AFTER STATEMENT and BEFORE STATEMENT will be executed only once but for each insert to check whether BEFORE EACH ROW and AFTER EACH ROW block exists or not in compound trigger the compound trigger will be invoked.

Will it not impact performance?

Please help..

Thanks in advance....

dowonderatwill · Answer 1 · 2020-09-20T01:10:24.543

I also come across this need to accumulate the individual inserts and after statement do insert in one go using forall. But in usual way of insert, as insert in itself is one statement, so it will call the after statement for each insert! Also tried with INSERT ALL into ... But that also fires the after statement on each "into ..".

To make all insert into one statement, it is mentioned here: Best way to do multi-row insert in Oracle?. So after making inserts as one statement then it will flush only after the threshhold or "after statement". In this way even though for each row will be called every time but heavy work will be done only at the time of flush (threshhold or after statement).

compound trigger - performance impact

1 Answers1