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

mjswart_at_yahoo.com (Michael Swart) wrote in message news:<22f04e15.0306060952.7860f82a_at_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