Here is the patch file for our (VT) local changes to the authz service to support that query change (discussed in the TCC meeting).
Since the parameter order had to be tweaked to accomodate our change, and since we implemented a static role-key cache to support the hardcoding of role key values into the query, the patch is somewhat complicated.
There's also a change to ehcache.xml that I'm not certain is related to the function of this patch at all, but I wanted to get this patch to you right away rather than spending a bunch of time verifying things.
Will and I worked together on redesigning the query itself, starting from a suggestion by Richard Quintin. Jihane did most of the work on implementing the role key cache, I believe. This patch tweaks the query for all DB implementations, but Oracle is the only one we've tested at production scale.
I'm guessing some actual stats on the improvement this query makes in performance would be worthwhile. I will try to dig those up from my email archive or I might resort to recalculating them if need be. It should be relatively straightforward to do so.
The patch applies cleanly against kernel 1.2.6 (the one paired with Sakai 2.8.1). My understanding is that this query or the role key cache would need to be made optional if this were to be included in 2.9. I'm guessing Will and/or Jihane are better equipped to help out with that effort than I would be. Otherwise, let me know what else I can do to help the effort to get this work into the community code.