I have an application which is heavily used on a network. The app is built around a table with 150,000+ records, and about 800 new records added every day.
The table has 16 indexes in the cdx, and the primary index gets screwed up every day. What happens is that by the end of the day about 10 out of 800 records are missing (meaning if I set order to my primary index, they are not found, but all other indexes find them).
Just for curiosity I added another index, same as my primary, and watched it for 3 days: this index is updated properly, nothing is missing, while my primary got screwed up again... I thought the problem is because of overuse, but I'm not sure.
Does anybody knows how to deal with it?