--- In firebird-support@yahoogroups.com, "Matt Clark" <bzugda@...> wrote:

>
> We have a heavily indexed (18 fields in 10 indexes) table on fb1.5
> with 32mil records and approx 35k being added throughout the day. Can
> anyone comment on the likelyhood of the indexes being corrupted within
> a day due to that volume of data being added ?
>
> I ask because a while ago we were able to confirm that at the end of a
> day trading a select and an update with the same criteria would return
> a different rowcount! Since then we've had a program of rebuilding
> loads of indexes daily and that seems to be controlling the problem
> but it's getting to a point where this is not practical so ideally I
> want to stop rebuilding the indexes daily. I'm doing several things to
> reduce the dependency on these indexes but ultimately if I can't
> explain the symptoms described I'm not going to be able to disable the
> rebuild on whatever indexes remain.
> Does it sound like index corruption or can you suggest another reason
> we might be getting the symptoms described ?

Index corruptions should not happen at all, let alone daily. If it
truly is index corruption, then I would be checking for hardware
issues. We have several hundred databases, and have not had corruption
of any description for over a year.

But it also makes no sense to me as a cause of the observed anomaly.
It the select and update are using the same criteria, then they would
(almost certainly) use the same plan to locate the record. If the
index was indeed missing data, then surely it would miss on both the
update and select.

Are you running both statements inside the same snapshot transaction?
(Transaction isolation may be hiding certain records from one or the
other). Do you have any update triggers that might (directly or
indirectly) deleting or inserting records thus effecting the counts?

Adam