Re: Function in a WHERE clause

A copy of this was sent to Shabba <shabba_at_nowhere.com> (if that email address didn't require changing) On Thu, 02 Sep 1999 16:03:27 +0100, you 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.

it'll be *much* faster to do b) above.

If you do a) make sure to code it as:

select rowid from any_table
where key_value = ( select my_functon(a_parameter) from dual );

that'll help the query plan generate a plan that runs my_function(a_parameter) ONCE per query instead of (as yours would) once per row per query. As written above, a) would run very slow on a table with lots of rows as te plsql function would be evaluated for each row (it could return a different answer each time generally speaking so that is the correct behaviour). As I've written it -- it should be evaluated once per QUERY (while the 2 queries appear to ask the same question they are in fact semantically very different questions) and held as a constant.

so, if you are doing this query inside of plsql, put the results of the function into a variable and use a bind variable.

If you are not doing this in plsql and bind variables are inconvienent -- then use the "= ( select f(x) from dual )" trick.

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Sep 02 1999 - 13:15:08 CDT