--- In firebird-support@yahoogroups.com, Radu Sky <skysword76@...> wrote:

>
> Thank you for your replies.
> The selectivity within the not null part was good, unique constraint so
> no duplicates. I did change the null values to some negative impossible
> so that the index will work but I have to drop the unique constraint and
> to impose unique by trigger.
> I was (wrongly) assuming that the null values will be also indexed (to
> either start or end of the index).
> However, I have another table with the opposite situation. It is a
> table for deliveries with a DELIVERED_DATE which is NULL for the
> undelivered goods and set to some date for the rest. The usual work is
> to select the undelivered (which are few comparing to the constantly
> growing delivered items), aka NULL values. In this case the index

works.

> Is the optimizer transforms the query "WHEN DDATE IS NULL" into
> something like "WHEN DDATE is not in index", hence the index will work?
> If so, the index asc or desc should not matter

I think there is a directive NULLS FIRST etc that may be useful here.
I would have to read up on it though. Although you could have a
headstart and read up on the syntax.

Otherwise, in cases where you have a lot of NULLs in a FK field, you
can often normalise it a bit better. Then your not null check becomes
a not exists check on an indexed table.

Adam