Forum OpenACS Development: Re: Very slow query

Collapse
4: Re: Very slow query (response to 1)
Posted by Don Baccus on
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?

Collapse
7: Re: Very slow query (response to 4)
Posted by Dirk Gomez on
Yeah the optimizer seems to take some weird decisions...with Barry's and mine approach the optimizer knows much quicker than one of the two subqueries usually yields very few rows.

I'll use Barry's code for the system I am working on because it doesn't require DDL.