How can I optimise my trigger for bulk insert scenario?

Question

I have created a trigger which updates a column in a table (say colX) based on value in another column (colY). The colY can be inserted or updated and if colX is empty it should be set with the value of colY. The problem is I have a file upload which inserts a bulk of record in this table with colY filled. Most of the times the colX is also filled but if its not filled in I expect this trigger to do it for me. This is my trigger:

CREATE OR REPLACE TRIGGER T_FILLCOLY
BEFORE INSERT OR UPDATE ON TAB1
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW

DECLARE
    
BEGIN
if (:new.COLX is not null and :new.COLY is null) then 
:new.COLY:= :new.COLX;
end if;

if (:new.COLA is not null and :new.COLBis null) then 
:new.COLB := :new.COLA;
end if;

END;
/

Is there any way I can make this trigger faster? Right now the upload is taking twice the time it takes without the trigger in place. Thank you.

Answer 1

You might try leaving colx and coly alone (no trigger) and defining a third virtual column which displays the result of your null logic. That should drop your load time back to what is was but at the cost of that logic having to be executed at query time. You could also try disabling the trigger, doing the load, update the table with your null logic, then enabling the trigger. That might run a bit faster.

Answer 2

How often do you load this bulk file? If this is a one-off exercise - or a regularly scheduled job which is run through a procedure - here is an alternate approach.

1. Disable the trigger
2. Upload the bulk file
3. Use set-based UPDATE statements to apply the business rules to the uploaded records
4. Re-enable the trigger

The set-based operation should be faster than the row-by-row firing of the trigger. However, you may need to tune that statement to get satisfactory performance. How much work you put in might depend on how often you run this process.