Jon, that sounds like good work. Also, there was a conversation on
web/db aseveral months back which concluded that "select count(*) from
dual where exists (select 1 from ... )" should always be at least as
fast as "select decode(count(*),0,0,1) from ...", not to mention more
portable, so you probably want to replace that too.
FYI, here's what my acs_permission.permission_p (Oracle) looks like
right now. As you can see, I have not ditched the
acs_object_party_privilege_map view yet as you have, despite Michael
B.'s comment in the source suggesting it...
function permission_p (
object_id acs_objects.object_id%TYPE,
party_id parties.party_id%TYPE,
privilege acs_privileges.privilege%TYPE
) return char
as
exists_p char(1);
begin
-- We should question whether we really want to use the
-- acs_object_party_privilege_map since it unions the
-- difference queries. UNION ALL would be more efficient.
-- Also, we may want to test replacing the decode with
-- select count(*) from dual where exists ...
-- 1/12/2001, mbryzek
-- I've gone ahead and replaced it the "select decode(count(*),0,0,1)
-- from ..." with the superior "select count(*) from dual where exists
-- (select 1 from ... )". However, mbryzek's comments re. union
-- vs. union all, above, are still quite cogent...
-- --atp@arsdigita.com, 2001/04/29 05:29 EDT
select decode(count(*), 0,'f', 't') into exists_p
from dual where exists (select 1
from acs_object_party_privilege_map
where object_id = permission_p.object_id
and party_id = permission_p.party_id
and privilege = permission_p.privilege
);
return exists_p;
end;
