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

You might improve performance marginally by changing the logic to

BEGIN
if (a!=b) then rv:=0 else rv:=1;
end if;
return rv;
end;

since NULL != NULL also doesn't return true.

I suspect you'd see most improvement by rewriting your queries to use the built in NVL function.

-- 
Niall Litchfield
Oracle DBA
Audit Commission UK
"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é

> :)

>

>