Can you please suggest any alternative to this that is faster?
The following are timing measures for 5000 inserts/deletes with and without the trigger.
Operation |Time without Trigger(msec) | Time with Trigger (msec) |% overhead
ItemTable Insert | 49 | 249 | 409
ItemTable Delete | 171 | 348 | 104
The update query inside a trigger is taking just 10 msec.
Trigger that I have added:create or replace trigger fast_sync_add_ir_trigger
after insert on ITEMTABLE
referencing new as newRow
for each row
BEGIN
update LatestWorking set is_stale = 1 where item = :newRow.item;
END;
create or replace trigger fast_sync_delete_ir_trigger
before delete on ITEMTABLE
referencing old as oldRow
for each row
BEGIN
update LatestWorking set is_stale = 1 where item = :oldRow.item;
update LatestReleased set is_stale = 1 where item = :oldRow.item;
END;
Yep, that's kinda expected. Adding triggers will slow your SQL down! A lot.
The best way to speed this up is to ditch the triggers.
How exactly you do this depends on how the code calling the inserts/deletes work. But you code that looks something like this:
begin
insert into ... / delete from ...
update LatestWorking ...
end;
/