Forum OpenACS Q&A: How to batch update security_inherit_p efficiently?
Any suggestions for speeding this up, e.g. by disabling some triggers and manually recreating the tables that they would have updated or something like that? Or could someone give me at least a short overview which triggers/tables are being involved?
This is on oracle and for a one-time migration, so the system is not being accessed by other users during the time the update runs.
should be much faster than doing 80,000 separate update statements.update foo set bar = 0 where pkey in (select pkey from foo where ...)
Check the triggers in, I believe, acs-objects-create.sql.
You may be able to drop the trigger then write a PL/SQL proc to create the map afterward.
But writing the proc may take longer than the day you'll save ...
maybe if you're lucky the massive update approach will only take 21.5 hours!
For the pl/sql loop, would something like "alter session disable commit in procedure" help?
Why not try it against 5 or 10 objects and see if the loop speeds up?
I don't know if you are running this in sqlplus or using some kind of db api calls; but you might want to try the other method and see if the speed is higher (eg if using sqlplus try via AOLserver/tcl, and vice versa).