Forum OpenACS Development: Re: Very slow query
Another approach: try looping on acs_object_context_index where ancestor_id = :old.object_id. This will loop over the object's descendent entries in the denormalized table. For each descendent, delete from the denormalized table where object_id = the loop variable value and ancestor_id = the old object id.
I'll use Barry's code for the system I am working on because it doesn't require DDL.