Subject Re: [firebird-support] How likely is corruption of indexes in this system ?
Author Thomas Steinmaurer

> 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 ?


I only can say that a customer of mine had a similar problem with FB
1.5.3 Classic on Windows.

Although he didn't have that many records, the same SELECT statement
gave a different result depending whether a particular index has been
used by the optimizer or not.

He worked around that issue by rebuilding the index on the most frequent
table in the night. After rebuilding the index, he got the same result
set for both scenarios. With and without index.

I suggested him to think about upgrading to v2, because some index
corruption bugs have been fixed there.

Best Regards,
Thomas Steinmaurer
LogManager Series - Logging/Auditing Suites supporting
InterBase, Firebird, Advantage Database, MS SQL Server and
NexusDB V2
Upscene Productions