Adam wrote:

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

AFAIK, NULLS FIRST, NULLS LAST are only used with ORDER BY statements
and the thing is that the index IS used when IS NULL and NOT used when
IS NOT NULL so NULLS FIRST, NULLS LAST will do no difference.
The fields I'm using indexing are not FK, simple fields with indexes for
faster selects and for the moment I'm doing the unique enforcement by an
EXISTS statement within a trigger.
I know that null means "no value,no type of value etc" but I can't
figure out why should not indexed and why IS NULL uses index and IS NOT
NULL doesn't. I tried to find answers to these in the IB documentation
and FB guides, but no luck. I assume that this is a standard SQL way and
I have to find a way to use index for both NULL and NOT NULL values.

Radu