Hmmm, interesting feedback.
It is clear when reading the code that avoiding JOINs in favor of "in (subselect)" or "= (subselect)" was an intentional strategy when the ACS 4 codebase was developed. And not just the core, in application packages as well. These two subselect constructs are used almost literally whenever possible.
This wasn't true in the ACS 3.x codebase.
Given the fact that use of such constructs seems clearly intentional, I've assumed aD knew for certain that Oracle would optimize them into the (theoretically possible, but not expressable in SQL) "join-exists"
style operation I mentioned above.
While apparently it is becoming clear that Oracle does not?
(I'm more familiar with the innards and details of PostgreSQL, and know for certain both subselect-style constructs are a poor choice in PG).
I'm doing a little digging around in the aD developer forum, and the best I can come up with thus far is that joining against complex views (like the permissions/party view) is handled poorly by Oracle and thus the "in" form may be faster. This, though, assumes that you can "slice" the view down to reasonable size in your "where" clause.
Thinking in terms of another thread, this may be one reason why so many variants of "read" (etc) perms are created, i.e. one-per-package. Using the above-described hack to get around slow JOINs on the permissions view works better if you can "slice" a small piece out of the generalized party/permissions view, and the more variants of "read", "write" etc you have the more likely it is that such a slice will be small.
In another words a couple of hack approaches to get around what might be fundamentally a relatively slow design ...
