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

From: Frank <fbortel_at_nescape.net>
Date: Mon, 24 Nov 2003 22:33:48 +0100
Message-ID: <bptt05$6bl$1@news3.tilbu1.nb.home.nl>

Jonathan Lewis wrote:

> You might go one step further and check if
> the (undocumented) function
> sys_op_map_nonnull()
> works a little faster.
>
> e.g.
> select where
> sys_op_map_nonnull(colA) = sys_op_map_nonnull(colB)
>
> the function seems to return a value that
> matches the TYPE of the column, but holds
> an impossible value if the column is null.
>
> I haven't checked it very carefully yet, so use
> at your own risk.
>
>
>
> "Frank" <fbortel_at_nescape.net> wrote in message
> news:bpl593$q4r$1_at_news4.tilbu1.nb.home.nl...
>

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

>
>
>

But that would still be a function call. Context switch - overhead - performance loss.

-- 
Regards, Frank van Bortel

Received on Mon Nov 24 2003 - 15:33:48 CST