Re: Why performancediff package--direct query?

another way use bitmap index
 (it indexes null values to)

"Keith Boulton" <boulke_at_globalnet.co.uk> wrote in message news:38ce217a.14862233_at_read.news.globalnet.co.uk...
> 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 Wed Mar 15 2000 - 00:00:00 CST