Ivan

> > If I have select . . from . . where invoice_number>=:INVNO --

the

> > plan uses
> > the index, but
> > select . . from . . where ((-1=:INVNO)or(invoice_number>=:INVNO))
> > doesn't
> > and the extraction is very slow.
>
> With expression (-1=:INVNO) you want to implement switch
> that will return all rows if INVNO is -1, right ?

Right!

>
> Although (invoice_number>=:INVNO) part can use index,
> (-1=:INVNO) can't. Because they are or-ed, IB must scan
> whole table anyway, so there is no point in using index.
>

I don't know anything about how the optimiser works so I can't really
argue. But I would have thought it would be possible to recognise
that -1 is a constant and take the view that anything ORed with (-
1=:PARAM) can still use and index? I don't seen why it scans the
whole table if its not referring to table contents.

> _You_ know that expression (-1=:INVNO) is independent on table

contents,

> but _IB optimizer_ does not. Even if optimizer would be able
> to identify such expression, it would not help much.
> The problem is that plan is chosen at the time command (your SP)
> is prepared (and parameter values are unkonwn), not when it is

executed.!

> But you expect that IB will choose one plan for INVNO=-1
> and different plan for INVNO<>-1.

I would have thought the fact that constants and not table contents
where involved would be enough to be usefull - regardless of the
values.

Regards
Paul