Hmmm...I don't see a drastic restructing of the permissions system in our near future though it may get some fiddling. I've thought of the separate table for modelling permission inheritance trick, too, and it might help considerably ...
They key, though, is to just avoid calling ad_perrmissions__permission_p in a where clause.
There's a bit of a struggle here in that the idea behind this proc is to abstract the check from the datamodel. I don't really like the idea of spawning dummy types and having permissions work on them, as then we'd have to worry about the scalability of the acs_object_types structure which itself is subjected to plenty of hierarchical queries.
But in general joining against the "all_object_party_permissions_map" directly and checking for permissions against the view is *much* faster if a lot of rows are involved. Use the "all" not the "acs_object_party_permission_map" though, as the latter's use of UNION will result in *very* slow queries, too.
In Oracle in particular the optimizer seems to like running permission_p on all rows before using join qualifiers to throw most of them away. Ugh. One way around this is to use a subselect to throw away the rows then use permission_p to check against those that survive the subselect.
