Pitfalls RANGE partitioning

Since RANGE partitioning can be significantly more complex than HASH partitioning, there are also more pitfalls to consider. It may not be distributed evenly like for HASH designs. You are responsible to design it along the rules of partitioning.

You have to analyze the data quality of your partition attribute. If you just use a time attribute because it is the only one means not that it is the only way. If the attribute is not used in your queries you may will end up in a bad performance for this table, because no pruning can be used. 

All additional options regarding dyn. range partitioning have certain limitations and besides benefits also some negativ impacts if you have not analyzed and monitored them well.

Threshold option

Tables with a dynamic others partition are monitored by a configurable background job which checks the current size of the partition in comparison to the defined limit and makes adjustments as required. If the OTHERS partition has exceeded the defined limit then all records in the OTHERS partition at the time the job runs are added to the new partition, the corresponding SQL statement is ADD PARTITION FROM OTHERS.

If you define a dyn. threshold of 50 Mio, a new range will be created with the min. value and the max. of the OTHERS partition.

You have defined ranges from 0 to 400,000. If you insert rows above the value 400,000 for CHANGENR they will be saved to the others partition. If the record count of the OTHERS partition (NOT the value of CHANGENR) will reach 50,000,000 a range will be created with the min value 400,000 and may be an odd number like 500,042 but again to illustrate it simple 500,000. A new empty OTHERS partition will be created for new entries without valid range as safe harbor.

It still can happen that a range can run full due to heavy usage of a certain number. This means multiple records of a CHANGENR. In this case, you still need to repartition your table and can adjust the threshold to a lower value.

Usage

This means this option can be useful if you are not aware of the min. / max. values of a partition and the only indicator is the records length and thus the partition size which leads to a certain number of records per partition.

Syntax

ALTER TABLE T ALTER PARTITION OTHERS DYNAMIC THRESHOLD 300000000;

SAP Documentation

Parameters

Partition parameters [partitioning] section of indexserver.ini :

dynamic_range_default_threshold (Default = 10000000)

dynamic_range_check_time_interval_sec (Default = 900s)

How you can check if threshold option is active?

SELECT TABLE_NAME, DYNAMIC_RANGE_THRESHOLD from TABLE_PARTITIONS

Pitfalls Threshold Option

Using the THRESHOLD property empty partitions are also removed during this process.

The creation of a new partition automatically stops the growing of existing partitions. You have to find out the right number of records for the threshold. This can be 300,000,000 records but this can also only 50,000,000 it depends on the scenario and usage of the table. It can also change over time due to higher workload. A proper monitoring is essentially for using RANGE partitioning. Do you have such a monitoring in place which monitors empty partitions, size of partitions, number of records per partition etc.? No? Let's get in touch to avoid unwanted situations (bad performance, error due to 2 billion records, right partition design, right threshold values etc.).