Query regarding tuning oracle trigger

Question

I have query regarding performance of OF,IF and WHEN clause in oracle trigger.Consider we have below trigger-

CREATE OR REPLACE TRIGGER WeightChange

   AFTER UPDATE ON Person

   FOR EACH ROW

BEGIN 

   IF :new.Weight > 250 AND new:Weight > old:Weight THEN 

      LogWeightChange(:new.PersonId, :new.Weight, :old.Weight); 

   END IF;

END WeightChange;

Now if I do following changes,

• like adding OF WEIGHT -WHEN( :new.Weight > 250 AND new:Weight > old:Weight )
• return Integer

Will any or all of above add to significant improvement of the trigger?

Answer 1

The WHEN (condition) and OF column trigger features can significantly improve trigger performance. Much of the performance penalty of triggers is the SQL and PL/SQL context switching, and by moving more logic into SQL those switches are avoided.

For example, let's start with a simple schema:

--Sample schema with 100K simple PERSON rows.
create table person
(
    id number not null primary key,
    name varchar2(100),
    weight number
);

insert into person
select level, level, 100 from dual connect by level <= 100000;
commit;

Enable or disable different trigger features:

--Create trigger that fires for all rows.
CREATE OR REPLACE TRIGGER WeightChange
   AFTER UPDATE ON Person
   FOR EACH ROW
BEGIN 
   IF :new.Weight > 250 AND :new.Weight > :old.Weight THEN 
      null;
   END IF;
END WeightChange;
/

--Create trigger that only fires for relevant rows.
CREATE OR REPLACE TRIGGER WeightChange
   AFTER UPDATE ON Person
   FOR EACH ROW
   WHEN (new.Weight > 250 AND new.Weight > old.Weight)
BEGIN 
   null;
END WeightChange;
/

--Create trigger that fires for all updates of WEIGHT.
CREATE OR REPLACE TRIGGER WeightChange
   AFTER UPDATE OF weight ON person
   FOR EACH ROW
   WHEN (new.Weight > 250 AND new.Weight > old.Weight)
BEGIN 
   null;
END WeightChange;
/

--No trigger.
drop trigger WeightChange;

The WHEN (condition) and OF column features can make UPDATE statements run almost twice as fast in the best case.

--With no trigger - FAST:
--0.749, 0.670, 0.733
update person set weight = 100;
rollback;

--With normal trigger - SLOW:
--1.295, 1.279, 1.264 seconds
update person set weight = 100;
rollback;

--With WHEN condition trigger - FAST:
--0.687, 0.686, 0.687
update person set weight = 100;
rollback;

--With WHEN condition, using a value that satisfies conditions - SLOW:
--1.233, 1.232, 1.233
update person set weight = 500;
rollback;

--With normal trigger, update irrelevant column - SLOW:
--1.263, 1.248, 1.248
update person set name = name;
rollback;

--With OF column trigger, update irrelevant column - FAST:
--0.624, 0.624, 0.609
update person set name = name;
rollback;