Forum OpenACS Development: Re: Scalability of acs_objects and acs_object_context_index
Also ... acs_object_context_index is weighing in at less than four times the number of objects. This is a table with three integer columns in it, a pretty skinny table, the actual storage used will be considerably less than that for the objects and auxillary tables.
Hmmm...one concern with your query (even though you don't want to talk about it :) is the number of physical reads. Looks like bumping the shared buffer space for Oracle is in order, no?
In the next few weeks I'll load up my nice new server with its nice new disks with a much larger number of objects than I've tested with in the past. I'll play around with expanding the hierarchy view in Oracle as we do in PG and see what effect that has.