Why MIN, MAX, SUM, and COUNT Occasionally Return Inconsistent Values for Cluster (Performance-enhanced) Instances?

To improve the performance of operators such as MIN, MAX, SUM, and COUNT, cluster (performance-enhanced) instances pre-aggregate data and compute block-level statistics in advance. This speeds up queries as they return these statistics directly without the need of reading data. If data on the same timeline is updated repeatedly but not merged, the block-level statistics may be inaccurate.

This issue may occur occasionally only when data on the same timeline is updated but not merged. You do not need to worry about this issue on non-cluster instances because they do not support pre-aggregation. To avoid this issue in cluster (performance-enhanced) instances, ensure that:

• Data on the same timeline is frequently updated.
• The values of MIN, MAX, SUM, and COUNT must be precisely calculated.

You are advised to add /*+ Exact_Statistic_Query */ at the beginning of the SQL statement, for example:

select /*+ Exact_Statistic_Query */ count(*) from mst where XXXX

The system will collect accurate values, but it will takes longer to query them.