Re: Function in a WHERE clause

In article <37CE91BE.63FCCDF_at_nowhere.com>,   Shabba <shabba_at_nowhere.com> wrote:
> Can anyone give a definitive answer to the following?
>
> In PL/SQL, a row has to be selected from the database on the condition
> that a key value matches a parameter which is first converted by
another
> function. Is there any advantage/disadvantage in doing the conversion
> before the select rather than in the WHERE clause of the SELECT?
>
> i.e. which (if either) of the following code segments would be better?
>
> a)
>
> SELECT rowid
> FROM any_table
> WHERE key_value = my_func(a_parameter);
>
> b)
>
> a_local_variable := my_func(a_parameter)
>
> SELECT rowid
> FROM any_table
> WHERE key_value = a_local_variable
>
> If anyone can let me know which is better, with a sensible reason why,
> I'd be grateful.
>
> Thanks
>
> Sh.
>
> PS This is on Oracle 7.3 for SunOS if it makes a difference.
>

I am of the opinion that it will make no difference. The presence of the function in the where or in pl/sql will not affect the explain plan of the query as your example is written. Now if the choice was to place the function on the key_value or to do a function on the variable to create the key_value then that is a differenct story since placing a function on key_value would disallow the use of an index.

--
Mark D. Powell -- The only advice that counts is the advice that  you follow so follow your own advice --

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Thu Sep 02 1999 - 12:27:22 CDT