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.
>
>

Personally, I'd prefer option (1) and use it frequently in most of our projects. There are a few situations which warrant the use of the second option.

If the column 'key_value' has an index, chances are that the index may not be used in generating the output. You can look up the explain plan for the index being used(or not used).  This becomes important when the table has many rows and optimization_cost needs to be at a minimum.

The advantage of the first option is the select statement can be executed from a variety of platforms: Visual Basic, Java or anything which supports SQL. The second option needs Pl/SQL or a language which supports Oracle stored procedures and functions.

Hope this helps,
Chandu

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