Re: Function in a WHERE clause

From: Connor McDonald <connor_mcdonald_at_yahoo.com>
Date: Thu, 02 Sep 1999 18:51:55 +0800
Message-ID: <37CE56CB.33AC@yahoo.com>

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

Probably the latter - Oracle may opt to run 'my_func' for every row processed in any_table (a trace would confirm)...

One option which I've seen in the past to avoid this is

SELECT rowid
FROM any_table
WHERE key_value = ( select distinct my_func(a_parameter) from dual );

--

---

Connor McDonald
"These views mine, no-one elses etc etc" connor_mcdonald_at_yahoo.com

"Some days you're the pigeon, and some days you're the statue." Received on Thu Sep 02 1999 - 05:51:55 CDT