>is it possible to create an index, but exclude records with values NULL and 0 in the index column for the index?
>
>I have a log table with millions of records, but maybe only 5-10% have a value in this specific field.
>And if I ever do a query using this value, only records with values <> NULL or 0 are relevant.

Hi Christian!

I haven't heard of this being possible, though I generally use an ancient Firebird version, so it might be a feature that have been introduced that I simply haven't noticed (CREATE INDEX MyIndex on MyTable(MyField) EXCLUDE (NULL, 0) or similar?). However, what's the point of excluding this state/value from being indexed? I don't think Firebird requires much space to store duplicate values in indexes and the latest versions of Firebird are a lot better to handle duplicate values in indexes than Firebird 1.0 and 1.5 were. Do you mean that you sometimes specify WHERE MyValue IS NULL or WHERE MyValue = 0? If so, the only reasonable way to prevent the index from being used (that I know of) is to change MyValue to MyValue+0.

Another thing that you may consider, is to split into two tables rather than one. If you have 100 fields, each of them 90% of the time NULL, and the 'NULLness' not dependent upon the other fields, then I would definitely not recommend you to create 100 additional tables. However, if the 100 fields were either all empty or all filled in, then one additional table to hold these 100 fields would probably be a good solution.

HTH,
Set