Re: A function used in select clause and where clause.

JusungYang_at_yahoo.com (Jusung Yang) wrote in message news:<130ba93a.0306061659.6a8cd8a9_at_posting.google.com>...
> mjswart_at_yahoo.com (Michael Swart) wrote in message news:<22f04e15.0306060952.7860f82a@posting.google.com>...
> > I am building a query that uses a function I wrote. It does something
> > similar to the following:
> >
> > select MYFUNCTION(SALARY)
> > from EMPLOYEES
> > where MYFUNCTION(SALARY) > 100000;
> >
> > My question is: does the MYFUNCTION(SALARY) get executed twice? If so,
> > how can I rewrite this query to avoid that?
> >
> > Anyones help would be appreciated and thanks in advance.
> > Michael Swart
>
> Yes, the function will indeed be executed twice - for each and every
> row in the table. The optimizer will just run a full table scan
> through the SQL statement. Strangely even this does not work:
>
> select c1 from (select MYFUNCTION(SALARY) c1 from EMPLOYEES) where
> c1>1000000;
>
> Two things you can try:
> 1. Function based index. If this is possible, the function will not
> even be used during the query.
> 2. Materilized view. Materialize the function, similar to FBI. But you
> have to take care of the refresh of course.
>
>
> - Jusung Yang

Thanks Jusung,

Unfortunately, I can't use a function based index since the example I provided is a simplified for clarity. In reality, the function takes arguments from more than one table and uses info from more than one table to do its calculations.

Materialized view is an idea but what I'm going to do is leave out the where clause and let the application filter for the desired rows.

Michael Received on Mon Jun 09 2003 - 16:13:38 CEST