How to test to VARCHAR2's for equality EFFICIENTLY ?

From: André Hartmann <andrehartmann_at_hotmail.com>
Date: Fri, 21 Nov 2003 14:14:04 +0100
Message-ID: <3fbe1046@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é
:) Received on Fri Nov 21 2003 - 07:14:04 CST