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

'select as' ? I obviously must have missed out something here, but I'm certainly prepared to learn something new!

"Ashish Mittal" <mittalashish_at_yahoo.com> wrote in message news:KCXb8.80548$AV5.386576_at_rwcrnsc51.ops.asp.att.net...
> 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:53:13 CET