Broken Indices

IBAnalyst since version 2.0 may report about broken or inconsistent indices. The detection of this cases is being made by checking index key count and record cound. If key count is less than records + versions, than the index is broken.

How it can happen?
At first, of course, it can happen when the database is broken, and some keys are missing. But the more realistic case for this inconsistency is indexing data when the data is being modified.
It can be easily reproduced, because index creation or re-activation passes three steps:

1. server moves data from the table to the temp file
2. data in the temp file is being sorted
3. server moves sorted data to the database as an index

If there is no write lock on the table during all three steps, modified (or inserted) data will not exist in the created index. And will never be found by the index search.
So, find the big table, apply "create index" on it, then wait until temp file for the sorting will be completely created in the temp directory, and after that insert some record in the table and commit. Then try to find this record with where condition. Null. Scary?
Yes, but this is fixed in Firebird 2.0 (by write locks on table during indexing).

p.s. gfix also may detect this type of index inconsistency.