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

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 Sat Jan 20 2001 - 04:01:24 CST