Why is the uniqueness check for cold data switched off in SAP HANA per default?

Thursday, July 12th, 2018

For ABAP applications using the Data Aging Framework, the default is set to “no uniqueness check” by SAP HANA.

Unique constraints for primary keys or unique indexes are normally checked across all partitions in case the partitioning is done over the artificial temperature that is not part of the primary key column of a table. This means that the cold partitions have to be accessed whenever data is inserted or updated.

To prevent a negative impact on performance, a database application can switch off the uniqueness check for cold data by setting an option in the partition specification.

With this relaxation, it is up to the application to either prevent duplicates (e.g. via GUIDs or number ranges) or to cope with duplicates.

If uniqueness checks for cold data are switched off, SAP HANA only ensures unique values in the hot partition. Conflicts between records in hot and cold, between two cold partitions, and between records in the same cold partition, are not prevented on database level in that case.