Re: PL/SQL functions in SQL where clauses - how to evaluate them as a late as possible

A couple of thoughts.

1. Native dynamic SQL
2. Get rid of the = 't' and replace it with a bind variable

Daniel Morgan

Dirk Gomez wrote:

> 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 - 16:57:09 CST