Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: Fastest query in PL/SQL function when values can be NULL

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

From: Daniel A. Morgan <dmorgan_at_exesolutions.com>
Date: Sun, 21 Jan 2001 01:01:52 -0800
Message-ID: <3A6AA580.F05E4C58@exesolutions.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?

Have you considered replacing the NULLs with a default value?

Daniel A. Morgan Received on Sun Jan 21 2001 - 03:01:52 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US