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

In article <3A6962C2.2A4_at_teleport.com>,
  GHouck <hksys_at_teleport.com> wrote:
> 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
>

You can create your SQL statement on the fly -->> using either dbms_sql or native dynamic sql. The idea is that the SQL statement is build based on the information on the input -->

        if ainp is null
        then
            sqlStmt := sqlStmt || ' and a is null ';
        else
            sqlStmt := sqlStmt || ' and a = ainp ';
        end if;

Chech out the syntax for dynamic SQL. The manual has good examples.

--
Michael Krolewski
Rosetta Inpharmatics
mkrolewski_at_rii.com
              Usual disclaimers


Sent via Deja.com
http://www.deja.com/