PL/SQL functions in SQL where clauses

Hi there,

I'm working on a system with finely-grained permissioning. The permissioning is being computed by a pl/sql function ("permission_p"). So there's a lot of sql queries that look like:

select <something>
  from <some tables>
  where <some where clauses>
  and permission_p(:current_user_id, object_id, 'access')='t';

The where clauses are _always_ much more restrictive and much cheaper to call than the permission_p. Yet it seems that the CBO is thrashing through the pl/sql function first. How can I change this behaviour?

On a related note: the permission_p function looks like this:

  select decode(count(*),0,0,1)

     into v_permission_p
     from dual
     where exists (select 1 from ...);

  if v_permission_p = 1 then
    return 1;
  end if;

  and so on.

Is there a way to save some cycles there? Received on Thu Feb 14 2002 - 19:50:20 CET