Re: Why performancediff package--direct query?

On Tue, 14 Mar 2000 01:28:54 -0800, Göran K <goranNOgoSPAM_at_champion.se.invalid> wrote:

>function har_transportor(cbutik_id varchar2 default NULL, clev_satt_id
>varchar2 default NULL, clevadress_id varchar2 default NULL) return
>boolean
>is
> cantal_tider integer;
>begin
> select count(*)
> into cantal_tider
> from vy_leveranstid
> where (butik_id=cbutik_id or cbutik_id is NULL)
> and (lev_satt_id=clev_satt_id or clev_satt_id is NULL)
> and (lev_adress_id=clevadress_id or clevadress_id is NULL);
> return cantal_tider>0;
>end har_transportor;
>--------------------

The problem may be because of the 'or xxxx_id is null' clause. I think this may be causing a full table scan. When using bind variables, the optimiser does not know the value so in the above example it must assume that all values are null in which case a full table scan is required. Given a literal value, it can know that an index may be used.

You should either build a statement for each possible input combination of values or use dynamic sql to build the statement to be executed. Received on Tue Mar 14 2000 - 00:00:00 CST