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

Did you try dynamic sql (dbms sql or native) ?

Marc

>>>>>>>>>>>>>>>>>> Oorspronkelijk bericht <<<<<<<<<<<<<<<<<<

Op 2001-01-20, 11:01:24, schreef GHouck <hksys_at_teleport.com> over het th ema
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 np 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 - 00:17:06 CST