I'm surprised Oracle can't do a better job with this one, but it's very very deadly for Postgres (and we execute the same query there.)
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.
Make sense?
