Hi Arno,
i can understand that index order doesn't help to filter
with this condition (because of there is no orderly branch/scope in
index tree):

where bdate >= somecalculatin(bdate)

But it will be nice if we can give a hit to force FB to do full-
index-traverse (orderless), because of there is no field dependence
other than index field in condition.
Full-scaning of some dozens of index pages will be much faster than
full-scaning of some thousands of data pages.
DB pages is fragmanted FB level + OS level, so each each page IO has
a cost about 10ms.


Thanks.

-Ali

--- In firebird-support@yahoogroups.com, "Arno Brinkman"
<firebird@a...> wrote:

> Hi,
>
> > where bdate between bdateblahblah and bdateblahblah
> >
> > it is not uses the index on bdate.
> >
> > but
> > when i use:
> > ...
> > where bdate>'01.01.1900' and
> > bdate between bdateblahblah and bdateblahblah
> >
> > then it uses the index on bdate (Query PLAN reports)
> >
> > is this correct behaviour?
>
> Yes, an index will be used to build a bitmap for dates where
> '01.01.1900'.
>
> > adding and extra condition helps to use index?
>
> Because the condition is a constant and no expression with an

other field from the same table.

>
> > or there is a plan report problem ( natural+index scan? )
>
> No, the "bdate between bdateblahblah and bdateblahblah" filter

will be handled after internally the

> records are fetched from disk.
>
> Regards,
> Arno Brinkman
> ABVisie