Kjell,

Are NULLs stored in an index? I believe not.

With regards,

Martijn Tonies
Upscene Productions
http://www.upscene.com

Download Database Workbench for Oracle, MS SQL Server, Sybase SQL
Anywhere, MySQL, InterBase, NexusDB and Firebird!

>> > Is it possible to make an index in FB ignore nulls? I mean, can the
>> > index be made to not store in the index those records with null in the
>> > indexed field?
>> >
>> > I have a 150 million table with some fields that will be 99% or more
>> > null, but in some situations I will have to lookup one specific record
>> > based on a non-null value of that field. Seems like such a waste to
>> > store all those nulls in the index... Without the index, the query
>> takes
>> > about an hour, which is not acceptable.
>>
>> If 99% of the records do not have this field, would it not make more
>> sense to create a separate table for that field(s), and use the record
>> ID to join to the full table?
>
> Interesting proposition. This is a schema from an OO framework, but it
> allows a lot of tweaking and I could modify the model to get the mapping
> you describe. I will have to give this some thought. Thanks for the idea!