Table partitioning is a data organization scheme in which table data is divided across multiple storage objects called data partitions.
In SAP HANA database, it is possible to split column-store tables horizontally into disjunctive sub-tables or partitions. The SAP HANA database supports several redistribution operations that use complex algorithms to evaluate the current distribution and determine a better distribution depending on the situation. Partitioning is typically used in distributed systems, but it may also be beneficial for single-host systems. Partitioning is transparent for SQL queries and data manipulation language statements.
In a distributed SAP HANA system, tables are assigned to an index server on a particular host at their time of creation, but this assignment can be changed. In certain situations, it is even necessary.
In SAP HANA side-by-side implementation, SLT will stop replication when SAP HANA table reaches 2 billion records as a non-partitioned table cannot store more than 2 billion rows.
Advantages of partitioning:
+ Load balancing in a distributed system
+ Overcoming the size limitation of column-store tables
+ Parallelization
+ Partition pruning
+ Improved performance of the delta merge operation
+ Explicit partition handling
SAP HANA supports:
- Hash Partitioning
- Range Partitioning
- Round-robin Partitioning
Hash & Round-robin partitioning do not require an in-depth knowledge of the actual content of the table unlike Range partitioning. For example, a range partitioning scheme can be chosen to create one partition for each calendar month.
For each Hash & Range partitioning specification, columns must be specified as partitioning columns. If the table has a primary key, these partitioning columns must be part of the key for both the cases.
With round-robin partitioning, new rows are assigned to partitions on a rotation basis to achieve an equal distribution.
Range partitioning is not well suited for load distribution.
In SAP HANA, we can create
- Single-Level Partitioning
= No nested Partitioning
- Multi-Level Partitioning
It can be used to overcome the limitation of single-level Hash and Range partitioning. Multi-level partitioning makes it possible to partition by a column that is not part of the primary key.
For simplicity, here I have discussed only Single-Level Partitioning with all the three partitioning options including screen-shots:
Sometimes, for business requirement, customer may require multi-level table partitioning.
But the best course of action depends on the current kind of partitioning they have.
- Do they have hash, range, or round-robin?
- Do they just want to have more partitions on the first level? If so, how many?
- Do they want to keep 5 partitions on the first level, and to create a new 2nd level for partitioning? If so, how do they want to design this level? Which partitioning criteria? How many partitions?
These are all questions the customer needs to answer.
Relevant Developer(s)/Consultant(s) can discuss about the pros and cons about the same.
The following SAP documentation can be used for info and best practices on partitioning : Table Partitioning : SAP Help and for the specific statement of ALTER TABLE partition clause : click here
If the table belongs to a standard SAP application, there may exist best practices from SAP, usually in the form of a SAP Note. Just use a SAP Note search using the name of the table and the term “Partitioning”.