Rule of thumbs

Rule of thumbs for the initial partitioning:

• min. 100 mio entries per partition for initial partitioning
• max. 500 mio entries per partition for initial partitioning
• if you choose too many partitions you can achieve a bad performance, because one thread per partition have to be triggered (e.g. you have 20 threads as statement concurrency limit and 40 partition have to be scanned which results in waiting for resources)
• if you choose too less partitions, it can be that you have to repartition pretty timely which means another maintenance window / downtime 
• recommendation: HASH partitioning on a selective column, typically part of primary key
• making sure that a single partition doesn't exceed a size of about 25 to 30 GB due to delta merge performance (SAP Note 2100010) 
  
• not too many empty partitions

Some of this rules can be checked by using the mini checks of SAP note 1969700.

HASH 

Use HASH partitioning if you have not a number, id or interval which is constantly increasing and used by the SQLs where clauses. If you are not aware of

the change rate and distribution, you can use HASH partitioning as a safe harbor. But use the number of partitions wisely! You can not easily add

partitions like for RANGE partitioning. The scalability is limited. A repartitioning can be expensive! Create the number of partitions wisely not too many and not too less. 

RANGE

Use RANGE partitioning if you have a time attribute or a change number. This means a integer value which is constantly increasing and used by the SQLs 

where clauses. 

Typical date/time fields (in ABAP dictionary the data type is another one compared to HANA DB):

GJAHR (4 chars, ABAP DDIC: NUMC, HANA : NVARCHAR)

UDATE (8 chars, ABAP DDIC: DATS, HANA : NVARCHAR)

TIMESTAMP (21 chars, ABAP DDIC: DEC, HANA : DECIMAL)

Typical other integer fields which can be used for RANGE partitioning (sure there are more):

KNUMV

CHANGENR

This means not that every table with such columns should be partitioned by this attributes. It depends on the distribution and selectivity. Every system is different and there is no silver bullet.

The advantage of RANGE partitioning is, that you can add new partitions within milliseconds without disturbing the business operations. It means RANGE partitioning is the smarter partitioning option. You can also rebalance the design by merging or splitting partitions. During the partitioning process only the defined and affected ranges will be touched. This allows you to redesign the partitioning without big downtimes. This applies to standard/offline partitioning. For online partitioning always the complete table has to be touched!

RANGE partitioning includes also in all normal cases a OTHERS partition which all data will be stored which has no valid range. There are some features regarding dynamic range options which will be handled in an own article.

Multilevel partitioning

If your system includes huge tables it might be wise to use more than one attribute. One on the first level and one on the second level. This depends on the affected table and columns if it makes sense or not. There are only rare scenarios where it makes sense to combine multiple attributes on one level.

Designing Partitions

Actually the online repartitioning is based on table replication. Tables with the naming convention _SYS_OMR_<source_table>#<id> are used as interim 

tables during online repartitioning operations. For details please read the “Designing Partitions” section in the documentation.

Summary:

1.   Use partitioning columns that are often used in WHERE clauses for partition pruning
   
2.   If you don’t know which partition scheme to use, start with hash partitioning
   
3.   Use as many columns in the hash partitioning as required for good load balancing, but try to use only those columns that are typically used in requests
   
4.   Queries do not necessarily become faster when smaller partitions are searched. Often queries make use of indexes and the table or partition size is 
5.   not significant. If the search criterion is not selective though, partition size does matter.
   
6.   Using time-based partitioning often involves the use of hash-range partitioning with range on a date column
   
7.   If you split an index (SAP names the CS tables also as index), always use a multiple of the source parts (for example 2 to 4 partitions). This way the
8.   split will be executed in parallel mode and also does not require parts to be moved to a single server first.
   
9.   Do not split/merge a table unless necessary.
   
10.   Ideally tables have a time criterion in the primary key. This can then be used for time-based partitioning.
    
11.   Single level partitioning limitation: the limitation of only being able to use key columns as partitioning columns (homogeneous partitioning)
    
12.   the client (MANDT/MANDANT) as single attribute for partitioning is not recommended - only useful in multi level partitioning scenarios with real multi clients environments

In the end if you want be on the safe site, just contact us. We will find a scalable design and may improve performance or also find a NSE design for your tables. It is a complex topic on for a proper design you need deep knowledge in SQL performance tuning, partitioning options and HANA is working in general. In the near future our new book will be released with all details regarding partitioning.

Details:

Blog: HANA Partitioning – 2 billion rows limitation – Part II: BSoH / S/4HANA

3234063 - How to partition/re-partition a table
3111531 - SAP HANA - How to convert an existing hash partitioned table to range.

2044468 - FAQ: SAP HANA Partitioning
2418299 - SAP HANA: Partitioning Best Practices / Examples for SAP Tables
2289491 - Best Practices for Partitioning of Finance Tables