Re: How to test to VARCHAR2's for equality EFFICIENTLY ?

"André Hartmann" <andrehartmann_at_hotmail.com> wrote in message news:<3fbe1046_at_olaf.komtel.net>...
> 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é
> :)

Suggested (by many) use of NVL is one of the best solutions. Another one:

WHERE a || '*' = b || '*'

Don't know which of them would be (marginally) faster. Granted, this colution may not work if (when) Oracle changes handling of empty strings. But I think in that case too many Oracle-based applications will not work.

Of course, you can use any character in place of '*'. Received on Tue Nov 25 2003 - 10:16:22 CST