André Hartmann wrote:
> Hi there,
>
> in my database schema there is a function that tests two VARCHAR2 values
> for equality. The function goes like this:
>
> (a IN VARCHAR2, b IN VARCHAR2)
> RETURN INTEGER
> IS RV NUMBER;
> BEGIN
> IF (a IS NULL) AND (b IS NULL) THEN
> rv := 1;
> ELSE
> IF (a = b) THEN
> rv := 1;
> ELSE
> rv := 0;
> END IF;
> END IF;
> RETURN rv;
> END;
>
> Why did I do it ? Well because the usual "=" operator does not say that two
> NULLs are equal ! to check this, just try "select 1 from DUAL where
> NULL=NULL".
>
> Now I see that my function gets called VERY often, many million times in a
> great variety of SQL statements that my application fires. Any idea how i
> could improve the performance of the function or maybe there is an entirely
> different approach to that ?
>
> Thanks,
> André
> :)
>
>
I'd say replace the call to the function
with "a = b or (a is null and b is null)".
Oracle will skip the null testing if a=b,
and this allows for other, hopefully better,
execution plans.
--
Regards, Frank van Bortel
Received on Fri Nov 21 2003 - 07:59:49 CST
