|Subject||Re: [firebird-support] Re: Missing records in a big table|
>> Hi allThanks for your reply
>> I'm a bit confused about these results
>> SELECT COUNT(*) FROM HBI_POS_TRAN = 681385
>> SELECT COUNT(*) FROM HBI_POS_TRAN WHERE PARENT_TRNO IS NULL = 681280
>> SELECT COUNT(*) FROM HBI_POS_TRAN WHERE PARENT_TRNO IS NOT NULL = 10
>> 681385 - (681280 + 10) = 95
>> I Seem to be missing 95 Records (or they seem to have PARENT_TRNO that
>> is neither Null nor Not null)
>> SELECT COUNT(*) FROM HBI_POS_TRAN WHERE (COALESCE(PARENT_TRNO,0) = 0)
>> = 681375 (This seems to be correct)
> Is the database in use while this is happenning and if so, are you
> using a read-committed transaction? If so, perhaps another transaction
> deleted a bunch of records and committed during the counts, but that
> would not explain why the coalesce worked
> Perhaps the foreign key index has been corrupted. This index is used
> to quickly identify the data pages that contain the null fields:
> SELECT COUNT(*)
> FROM HBI_POS_TRAN
> WHERE PARENT_TRNO || '' IS NULL
> This will prevent the index from being used (so it will probably take
> longer). If it comes up with the right number, then drop the foreign
> key constraint and redefine it.
I have confirmed that this is a corrupted index on the PARENT_TRNO Field.
Does anyone have an idea why the index is getting corrupted?, could it
be because the value in PARENT_TRNO is null 99.9 % of the time?
I'm inserting about 6.000 records a day into this table, under
concurrency transaction control.