How to fix the Oracle error ORA-36260: (XSAGHIERPART00) Aggregating from partition %J to partition %J over hierarchy workspace object creates an increase in sparsity.?

In this post, you’ll learn more about the Oracle ErrorORA-36260: (XSAGHIERPART00) Aggregating from partition %J to partition %J over hierarchy workspace object creates an increase in sparsity. with the details on why you receive this error and the possible solution to fix it.

Oracle Error Description

ORA-36260: (XSAGHIERPART00) Aggregating from partition %J to partition %J over hierarchy workspace object creates an increase in sparsity.

Reason for the Error ORA-36260: (XSAGHIERPART00) Aggregating from partition %J to partition %J over hierarchy workspace object creates an increase in sparsity.

In the partition creation / aggmap creation a situation developed such that when aggregating over a particular dimension of the aggmap a partition boundary was crossed such that the sparsity of the target partition included dimensions that were not in the sparsity of the source partition. Since the process of aggregation always densifies rather than sparsifying this is an extremely suboptimal design and it is not supported by the aggregate system.

How to fix the Error ORA-36260: (XSAGHIERPART00) Aggregating from partition %J to partition %J over hierarchy workspace object creates an increase in sparsity. ?

You can fix this error in Oracle by following the below steps

Set up your partitioning such that for any partition boundary the source and target partitions of the aggregation will always move towards denser partitions. For any child (c) and any parent (p) where (c) and (p) are in different partitions it must be the case that the parent partition contains no dimension in the composite that the child partition does not contain in its composite.