Hi Jon
Although I haven't played around much with ACS 4 much, I have spent the last 3 years working with Oracle in a bank, designing and implementing a risk management system. A big problem is things like which trade belongs to wich department, and departments are part of other departments etc.
I checked out some random bits I found at google ( just searcing for the name of the view actually turned up quite a few hits. )
http://www.google.com/search?q=cache:acs40.arsdigita.com/doc/sql/display-sql%3Furl%3Dacs-permissions-create.sql%26package_key%3Dacs-kernel+acs_privilege_descendant_map+&hl=da
From the small time I spend on it, it looks like that the view
create or replace view acs_privilege_descendant_map
as select p1.privilege, p2.privilege as descendant
from acs_privileges p1, acs_privileges p2
where p2.privilege in (select child_privilege
from acs_privilege_hierarchy start with privilege = p1.privilege
connect by prior child_privilege = privilege)
or p2.privilege = p1.privilege;
is used alot, through the acs_object_grantee_priv_map. Is that also the case in the OpenACS distribution ? Anyway, on Oracle 7.3 which I was working on, views on views on views.. etc generally was very difficult to get working. The Oracle optimizer generally didn't do that great.
For what its worth I would suggest two possible solutions that could be explored.
1. Merge the views by hand. That is, take the view acs_object_party_privilege_map and copy paste the underlying views into this. Of course this makes the code less reusable but you don't have to break the structure of the OpenACS system. It is much easier to optimize this by hints, than on a series of dependent views.
2. The connect by view above, could also be part of the problem. A solution we used to speed performance was to create a table, with the contents of our department structure in. That way we could build indexes on the relevant keys.
Again, I haven't spend time on OpenACS 4 ( only 3.24 ) so the above may be nonsense, but I thought the problems described here where similar to some I had experienced earlier.
