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

Dirk Gomez <usenet_at_dirkgomez.de> wrote in message news:<m3n0y4tqzy.fsf_at_colorado.arsdigita.de>...
> 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

Try something like

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

             from dual )

It might help.

Mike Received on Thu Feb 21 2002 - 11:25:31 CST