Subject Re: [IB-Architect] Query Optimiser
Author Ivan Prenosil
> 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 ?

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.

_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.