Inconsistency in Caché Database: Mismatch Between Total Count Based on Unique Identifiers, Deduplicated Count, and Conditional Query Results

Question

Question

ha haha · Aug 13

In the Caché database, when calculating the total count based on the unique identifier of a record, the quantity is over 1.2 million. After removing duplicates based on the unique identifier and then calculating the total count, the quantity is over 400,000. When grouping by the unique identifier, it can be observed that the count for this identifier is not one. However, when performing a conditional query based on the identifier, only one record can be retrieved. Why is this the case?

Product version: Caché 2016.1

Discussion (4)3

Log in or sign up to continue

score 1 · Answer 1 · 2025-08-13T04:52:56-04:00

Evgeny Shvarov · Aug 13

Rebuild indices, this could be the case.

1 0

score 0 · Answer 2 · 2025-08-14T04:03:59-04:00

ha haha · 12 hr ago

I'm not quite clear, could you please elaborate?

0 0

score 1 · Answer 3 · 2025-08-14T08:15:11-04:00

In SMP (System Management Portal), you step to EXLORER and then step into SQL
where you select your TABLE. and can rebuild index

Furthermore, every persistent class has by default
• classmethod %BuildDeferredIndices
• classmethod %BuildIndices
• classmethod %BuildIndicesAsync

Next variant : use $SYSTEM.OBJ.ValidateIndices()
Details described here Fix broken index 8 years ago, still valid

score 0 · Answer 4 · 2025-08-14T05:23:31-04:00

Now my problem is that during data migration, duplicate data appears in the queries. Since pagination is manually controlled, I have to perform deduplication after migrating the data to the target database. Because the data volume is large, this is very time-consuming. Is there a good solution?