Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> PL/SQL functions in SQL where clauses - how to evaluate them as a late as possible
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 Wed Feb 20 2002 - 03:57:37 CST