I've posted a trace file on three different queries at
http://www.goliath.org/acs/trace-permissions.txt. The three queries you'll see are:
- group permission check using IN (ie. my version) (232 rows fetched)
- group permission check using JOIN (ie. Jon's version) (335512 rows fetched)
- group permission check using JOIN, without specifying privilege (ie. multirow select of all privileges of :grantee_id on :object_id) (561 rows fetched)
An interesting result is that query #3 is much faster than #2, which leads me to believe the real problem isn't with JOIN vs IN, but is with the way that "privilege" is indexed (or not). That's why I didn't include the COUNT(*) vs SELECT 1 FROM DUAL wrapper in the queries, because it doesn't seem relevant to the performance issue.
However, if someone could take a look at the trace file and help explain the results, I'd appreciate it, as I just learned how to do this since Don asked for it. =) Looking at the data model, I can't figure out where the problem is. It looks like privilege should an indexed column of acs_privileges, but since acs_object_grantee_priv_map is a view of a view, I can't really tell what's going on with privilege.
