Re: Fastest query in PL/SQL function when values can be NULL

From: Peter <P.Slingerland_at_IMN.nl>
Date: Mon, 22 Jan 2001 08:09:44 +0100
Message-ID: <94gm8f$agf$1@nereid.worldonline.nl>

Hello,

I would try something like this:

function myfunc ( ainp, binp, cinp, dinp ) returns integer   is
    select x into xrtn from mytable

    where  nvl(a,'~~') = nvl( ainp, '~~') and
           nvl(b, '~~') = nvl(binp, '~~') and
           nvl(c, '~~') = nvl(cinp, '~~') and
           nvl(d, '~~') = nvl(dinp,'~~');

    return xrtn;
  end;

for the '~~' you can choose any one or combination of characters as long as they are not likely at all to occur as characters in the table or in the parameters.

To speed up things (I don't think the unique index will now be used to access the table), you could considerer to create a function based index (as of db version 8.1.5).

Peter.

"GHouck" <hksys_at_teleport.com> wrote in message news:3A6961F4.5BA5_at_teleport.com...
> Fastest query in PL/SQL function when values can be NULL
>
> If I have a table with 5 columns such as:
>
> mytable (
> x unique integer (to be rtnd by function based on a,b,c,d)
> a varchar2
> b varchar2
> c number
> d number
> )
>
> and there is a unique index on (a,b,c,d), but any
> or all of 'a','b','c' or 'd' them can be NULL values.
>
> The function (eg, myfunc) is called with values ('ainp','binp',
> 'cinp' & 'dinp') to compare to the tabled values, and
> return the 'x' value for that row.
>
> Currently, we have a large chunk of 'if/then/else' code
> which determines which of the 'a','b','c' & 'd' are NULL,
> and executes the appropriate (1 of 15) SELECT statement
> based on that logic. Ugly, but it works.
>
> This is all to avoid functions or (... (a=ainp or ainp is NULL))
> in the WHERE clause, which dramatically slows down the
> SELECT (and the function, which is called 100's of thousands
> of times).
>
> Ideally, one would like something like this to work:
>
> -- -----
> function myfunc ( ainp, binp, cinp, dinp ) returns integer
> is
> select x into xrtn from mytable
> where a = ainp and
> b = binp and
> c = cinp and
> d = dinp;
> return xrtn;
> end;
> -- -----
>
> But, with the possibility of NULLs in both the tabled and
> passed parameter values, it obviously doesn't work.
>
> Is there a faster way to formulate a PL/SQL query, given
> the values as parameters that are to be compared to the
> indexed table values, to get around the NVL() function,
> the 15 IF/THEN/ELSE/SELECT construct, or the
> (OR colval IS NULL) test?
>
> Thanks,
>
> Geoff Houck
> systems hk
> hksys_at_teleport.com
> http://www.teleport.com/~hksys
Received on Mon Jan 22 2001 - 01:09:44 CST