Subject Re: select birthdays form the next several days
Author Ali Gökçen
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.



--- In, "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