PL/SQL functions in SQL where clauses - performance problems

Hi there,

(Oracle 8.1.7, Solaris)

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';

permission_p is well-tuned and performs very, very quickly. The problem though is: The "regular" 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 assuming that permission_p is very, very restrictive or can be called at almost no cost.

Even when I do stuff like:

select * from (
  select field1, field2, expensive_sql_function(param1,param2) as result
  from ...
  )
  where result='t';

the optimizer seems to rearrange my statement to compute expensive_sql_function very early during the process.

I tried "associate statistics" with high costs and/or low selectivity. And I tried the ORDERED_PREDICATE hint. But both for no avail.

cheers Dirk Received on Mon Feb 25 2002 - 04:59:17 CST