Forum OpenACS Development: File storage query modification (Oracle)

We have a production site running Classic ACS4 with about 100,000 acs_objects. We noticed that the main file storage query was running pretty slowly and that a dramatic improvement could be achieved by removing the permission clause. It appears that Oracle wasn't being smart about running the permission check late in the game. Since the expensive permission check should be run on as few rows as possible, we've re-arranged the query to coax Oracle into doing the right thing.

A quick glance at the head of CVS for packages/file-storage/www/index-oracle.xql suggests that this may be a problem for OpenACS4 as well, so I'll post our re-arranged query:

select file_id,name,live_revision,path,type,last_modified,
       content_size,ordering_key,folder_id from (
select i.item_id as file_id,
       r.title as name,
package_id])) as path,
       r.mime_type as type,
       to_char(o.last_modified,'YYYY-MM-DD HH24:MI') as last_modified,
       dbms_lob.getlength(r.content) as content_size,
       1 as ordering_key,
       i.parent_id as folder_id
from   cr_items i, cr_revisions r, acs_objects o
where  i.item_id       = o.object_id
and    i.live_revision = r.revision_id (+)
and    i.parent_id     = :folder_id
and    i.content_type = 'content_revision'
) where 't' = (select acs_permission.permission_p(file_id, :user_id,
'read') from dual)
select file_id,name,live_revision,path,type,last_modified,
       content_size,ordering_key,folder_id from (
select i.item_id as file_id,
       f.label as name,
       0 as live_revision,
       content_item.get_path(f.folder_id) as path,
       'Folder' as type,
       NULL as last_modified,
       0 as content_size,
       0 as ordering_key,
       f.folder_id as folder_id
from   cr_items i, cr_folders f
where  i.item_id   = f.folder_id
and    i.parent_id = :folder_id
) where 't' = (select acs_permission.permission_p(folder_id, :user_id,
'read') from dual)
order by ordering_key,name
The trick here is that we push the UNION'ed queries into views on the fly, and then do a correlated subquery for permission. You'd think we could just do "...where acs_permission.permission_p() = 't'" instead of the "select ... from dual" construct, but in the former case we get no performance improvement. Presumably the Oracle optimizer is "simplifying" the query behind the scenes. I did briefly fool around with hints to see if I could force Oracle not to re-arrange the query, but without success.

In any case, we did see a dramatic improvement with form above.

Posted by Don Baccus on
This look very useful, thanks ...