>> Hi all
>>
>> 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.
>
> Adam
>
>
>

Thanks for your reply
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.

Magni