Nando Dessena wrote:

>>>- the general case would use that, but I'm not sure that the optimizer
>>>can always tell if two index scans are better than a natural scan.
>
> KR> I didn't understand that last bit.
>
> for "general case" I mean expressions like "somefield <> somevalue"
> (not related to is null/is not null).

[snipped excellent explanations]

Thanks! I understand now. :-)

> I think nulls might be placed at the beginning of an index regardless
> of the index direction.

OK, in that case I see two possible reasons for what I'm seeing. Either

1) FB sorts nulls at the end of the index regardless of asc/desc and
since an index can't be traversed backwards it cannot be used for "is
not null", or

2) FB sorts nulls at the beginning of the index regardless of asc/desc
and could possibly use it for "is not null", but for some reason does not.

If it's 1 then I'd just like to mention that FB could actually use the
index anyway, returning records from the beginning of the index until it
finds the first null. It would be interesting to know why FB doesn't do
this.

If it's 2 then it would be interesting to know why the index isn't used,
since it appears straightforward for FB to use it.

Thanks again,
Kjell
--
--------------------------------------
Kjell Rilbe
Adressmarknaden AM AB
E-post: kjell.rilbe@...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64