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

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.

>