Hi Experts,
I am working on a 12.2 database on an application where almost all of the business logic is implemented in triggers. Some tables have >30 triggers and it (obviously) has a *major* impact on performance. Updates can take up to a second for a single row! The triggers are out of my control, they are part of the suppliers application. There is a whole mix of row and statement triggers, compound, before, after... you name it, it's there
I have noticed something strange that I cannot explain, but what I would like to understand. Hopefully you can share some of your insights.
When doing updates using a single update statement that would update several thousand records, performance is very poor. E.g.
update table
set field = 'value'
where key in ( select key
from another_table
where ... )
But if I re-write this as a pl/sql for loop, the performance is significantly better (still slow, but up to 5 times faster than the above single update statement):
for c in ( select key
from another_table
where ... )
loop
update table
set field = 'value'
where key = c.key;
end loop;
I have checked the amount of updates (sql%rowcount). Both queries have the exact same result...
I've always understood that a single sql statement should be faster than row-by-row processing. However in this extreme case, it seems that individual updates are a lot faster.
Can you help me understand why this is?
Thanks!
Rop