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

let us say your original query was

Select * from table1,table2 where
permissions_p(table1.col1,table1.col1)='Q' and table1.col2=table2.col2.

Your problem is that for each possible record in table1, table2 the function is being evaluated. (Cartesian nested loops).

The same query can be written as

select * from
(
select table1.col1 as tc1, table2.col1 as tc2 from table1,table2 where table1.col2=table2.col2
) where permissions_p(tc1,tc2)='Q'

In this case, the inner query will be evaluated first and will return all rows where table1.col2=table2.col2.

Only after this result set is generated will the outer condition be used - effectively forcing an order of evaluation.

"Keld Nielsen" <keldnielsen_at_image.dk> wrote in message news:4nVb8.15036$5L3.637395_at_news010.worldonline.dk...
> strange (select) statement. Can you PLEASE elaborate ?
>
> "Ashish Mittal" <mittalashish_at_yahoo.com> wrote in message
> news:iTJb8.75447$AV5.376151_at_rwcrnsc51.ops.asp.att.net...
> > Well, one way to have the function evaluated last would be to do
> >
> > select * from
> > (
> > select * from <tables> where <normal where clauses>
> > )
> > where permission_p(param_column)
> >
> > However, I think it is EXTREMELY rare for this to be needed. Can you
 PLEASE
> > examine whether the permissions_P can be made a part of the sql. Can you
> > post the full code of permissions_p
> >
> > Ashish
> > "Dirk Gomez" <usenet_at_dirkgomez.de> wrote in message
> > news:c8a033bd.0202150955.34d721bf_at_posting.google.com...
> > > Hi Adrian,
> > >
> > > no the SQL statement in the PL/SQL function is pretty much tuned. It
> > > works amazingly quick.
> > >
> > > What hurts is that the PL/SQL function in the where clause usually is
> > > the _least_ selective predicate and I want it to be evaluated as late
> > > as possible. But 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 18 2002 - 01:09:14 CET