Let`s assume we have following task – make changes to the data in a large table that has consistency checker in a trigger. Let`s look at the ways to do it in a most efficient way.

Use test database in a simple recovery mode. Create two tables and fill them by random data.

Product table:

Finally we have table with about 5 million product records

Create order table that references the product table

Orders may have close date (closed orders):

20 million orders

And orders may not have close date (not closed orders):

About 5 million records

Let`s say we have a rule that says:

“Record in a product table can’t be modified unless there are not closed orders related to its products.”

If we want to change names for products with id <= 1M we have to wait some time:

45 seconds duration on my test server

Can we improve it?

Split update for some small bunches of 100 thousands records:

This loop worked 10 times and took 15 seconds to run. So we got a 3 times improvement. Not that this is specific to the server.

Can we speed it up more? If we are sure that our changes don’t require trigger’s check we can switch off the trigger. But then the trigger will not work for any other users too.

Is there a possibility to disable a trigger in our current session only?

Yes! Add the following hint to the trigger:

Try it now:

Running time – 12 seconds

Because we set special flag context_info for our session, trigger doesn’t fire for our session. But it fires for all other users`s sessions that do not have this flag enabled.

Finally we have an option to run mass DML queries bypassing trigger restrictions.

by Alexey Tikhomirov