Skip to Content
Technical Articles
Author's profile photo Laszlo Thoma

Collected information regarding partitioning in SAP HANA (with examples)

last updated: 2023-07-25

Introduction

There is huge knowledge available about SAP HANA partitioning, but the knowledge is very fragmented (Help Portal, Blog post, SAP KBA, SAP Note, other). In order to simply and make content consumption easier I have created a centralized page, a single source of the available information regarding SAP HANA partitioning. Further I would like to help you with partitioning exercises in SAP HANA, express edition to demonstrate the process.

Table of Content

  1. Task 1. – Exercise
    1. Prerequisites
    2. Background
    3. Exercise 1. – Partitioning “SBOOK” table (HASH, single-level)
    4. Exercise 2. – Increase the total number of partitions in “SBOOK” table (HASH, single-level)
    5. Exercise 3. – Partitioning “SBOOK” table (RANGE, single-level)
    6. Exercise 4./A. – Adding new RANGE partition (RANGE, single-level)
    7. Exercise 4./B. – Dropping specific RANGE partition (RANGE, single-level)
    8. Exercise 5. – Partitioning “SBOOK” table (HASH / HASH, multi-level)
    9. Exercise 6. – Increase the total number of partitions in “SBOOK” table (HASH / HASH, multi-level)
    10. Exercise 7. – Partitioning “SBOOK” table (HASH / RANGE, multi-level)
    11. Exercise 8. – Adding new RANGE partition (HASH / RANGE, multi-level)
    12. Exercise 9. – Partitioning “SBOOK” table (RANGE / RANGE, multi-level)
    13. Exercise 10. – Adding new RANGE partition on the first level into a multi-level-partitioned table (RANGE / RANGE, multi-level)
    14. Exercise +1. – Merge partitions (partitioned table to non-partitioned)
    15. Conclusion
  2. Task 2. – Documentation Library
    1. Where to find documentations in SAP Help Portal?
    2. Which is the master SAP Knowledge Base Article of the SAP HANA partitioning?
    3. Before Partitioning
    4. Parameters
    5. Which best practices are available regarding SAP HANA partitioning?
    6. Which other documentation is important regarding SAP HANA partitioning?
    7. Which SAP HANA Alert IDs belongs to the partitioning topic?
    8. Which typical SAP HANA error belongs to the partitioning topic?
    9. What are the available tools for partitioning?
    10. What are the known issues?
    11. What blog posts can be helpful?
    12. Other articles in this series
    13. Other articles in connected series
    14. Useful bookmark
  3. Task 3. – Use Cases
  4. Task 4. – Recommendation / Best Practice / Consideration

Task 1. – Exercise


Prerequisites


Background

During the exercises I will use test database SFLIGHT. More information can be find:
SAP HANA, express edition and SFLIGHT demo database, modeling – Complete Tutorial

The SFLIGHT schema is in the Catalog folder.


Exercise 1. – Partitioning “SBOOK” table (HASH, single-level)

Checking how much data can be found in the table.

SELECT COUNT(*) FROM "SFLIGHT"."SBOOK"

In production system the amount of data (~1 million row) should not be partitioned. It will be partitioned to demonstrate the process (test system).

Checking the data itself.

SELECT TOP 1000 * FROM "SFLIGHT"."SBOOK" ORDER BY "BOOKID"

“BOOKID” column seems to be a good choice for HASH partitioning.

ALTER TABLE "SFLIGHT"."SBOOK" PARTITION BY HASH ("BOOKID") PARTITIONS 2;

The partitioning was successful and the data distribution is also perfect.


Exercise 2. – Increase the total number of partitions in “SBOOK” table (HASH, single-level)

Re-partition the table with 4 partition. Multiply the actual partition number 2 => 4!

ALTER TABLE "SFLIGHT"."SBOOK" PARTITION BY HASH ("BOOKID") PARTITIONS 4;

The partitioning was successful and the data distribution is also perfect.


Exercise 3. – Partitioning “SBOOK” table (RANGE, single-level)

Checking the data itself.

SELECT DISTINCT "FLDATE" FROM "SFLIGHT"."SBOOK" ORDER BY "FLDATE"

“FLDATE” column seems to be a good choice for RANGE partitioning.

Partition/re-partition the table to be a single-level partitioned table.

ALTER TABLE "SFLIGHT"."SBOOK" PARTITION BY 
RANGE ("FLDATE") 
(PARTITION 20100101 <= VALUES < 20110101, 
 PARTITION 20110101 <= VALUES < 20120101,
 PARTITION 20120101 <= VALUES < 20130101,
 PARTITION OTHERS
);


Exercise 4./A. – Adding new RANGE partition (RANGE, single-level)

Adding new RANGE partition to an already single-level partitioned table.

ALTER TABLE "SFLIGHT"."SBOOK" ADD PARTITION 19900101 <= VALUES < 20100101;

❗Remark: The records (18) were moved to the new partition (where belongs to) from the OTHERS partition.


Exercise 4./B. – Dropping specific RANGE partition (RANGE, single-level)

Dropping specific RANGE partition from a single-level partitioned table.

ALTER TABLE "SFLIGHT"."SBOOK" DROP PARTITION 19900101 <= VALUES < 20100101;

❗WARNING: Dropping the partition won’t move the data back to the OTHERS partition. All data is deleted when dropping a partition.

SAP HANA Dynamic Tiering: Administration Guide – Drop a Partition


Exercise 5. – Partitioning “SBOOK” table (HASH / HASH, multi-level)

Partition/re-partition the table to be a multi-level partitioned table.

ALTER TABLE "SFLIGHT"."SBOOK" PARTITION BY 
HASH ("BOOKID") PARTITIONS 2, 
HASH ("CUSTOMID") PARTITIONS 2;

The partitioning was successful and the data distribution is also perfect.

❗Remark: Scenario had been tested in case of performance perspective.

⌛- – > Performance results (HASH 2 BOOKID; HASH 2 CUSTOMID): ~ 1.5 seconds


Exercise 6. – Increase the total number of partitions in “SBOOK” table (HASH / HASH, multi-level)

Re-partition the table with 4 partition (on second level). Multiply the actual partition number 2 => 4!

ALTER TABLE "SFLIGHT"."SBOOK" PARTITION BY 
HASH ("BOOKID") PARTITIONS 2, 
HASH ("CUSTOMID") PARTITIONS 4;

The partitioning was successful and the data distribution is also perfect.

❗Remark: Scenario had been tested in case of performance perspective. (multiple / divider rule)

⌛- – > Performance results (HASH 2 BOOKID; HASH 4 CUSTOMID): ~ 1.0 seconds
⌛- – > Performance results (HASH 2 BOOKID; HASH 3 CUSTOMID): ~ 4.0 seconds !!!!!


Exercise 7. – Partitioning “SBOOK” table (HASH / RANGE, multi-level)

Partition/re-partition the table to be a multi-level partitioned table.

ALTER TABLE "SFLIGHT"."SBOOK" PARTITION BY 
HASH ("BOOKID") PARTITIONS 2, 
RANGE ("FLDATE") 
(PARTITION 20100101 <= VALUES < 20110101, 
 PARTITION 20110101 <= VALUES < 20120101,
 PARTITION 20120101 <= VALUES < 20130101,
 PARTITION OTHERS
);


Exercise 8. – Adding new RANGE partition (HASH / RANGE, multi-level)

Adding new RANGE partition to an already multi-level partitioned table.

ALTER TABLE "SFLIGHT"."SBOOK" ADD PARTITION ("FLDATE") 19900101 <= VALUES < 20100101;


Exercise 9. – Partitioning “SBOOK” table (RANGE / RANGE, multi-level)

To be able to do the task, we need to modify the database table slightly. We create a copy from the original table.

-- table copy

CREATE COLUMN TABLE "SFLIGHT"."SBOOK_COPY" LIKE "SFLIGHT"."SBOOK" WITH DATA;

-- create new columns

ALTER TABLE "SFLIGHT"."SBOOK_COPY" ADD (FLDATE_YEAR NVARCHAR(4));
ALTER TABLE "SFLIGHT"."SBOOK_COPY" ADD (FLDATE_MONTH NVARCHAR(2));
ALTER TABLE "SFLIGHT"."SBOOK_COPY" ADD (FLDATE_DAY NVARCHAR(2));

-- update new columns content based on FLDATE column

UPDATE "SFLIGHT"."SBOOK_COPY" SET FLDATE_YEAR = YEAR(FLDATE);
UPDATE "SFLIGHT"."SBOOK_COPY" SET FLDATE_MONTH = MONTH(FLDATE);

-- recreate primary key (to be able to use FLDATE_YEAR column as a first level partitioning column)

ALTER TABLE "SFLIGHT"."SBOOK_COPY" DROP PRIMARY KEY;
ALTER TABLE "SFLIGHT"."SBOOK_COPY" ADD PRIMARY KEY (MANDT,CARRID,CONNID,FLDATE,BOOKID,FLDATE_YEAR);

After the modification the partitioning action can be done.

ALTER TABLE "SFLIGHT"."SBOOK_COPY" PARTITION BY 
RANGE (FLDATE_YEAR) 
(PARTITION VALUE = 2010, 
 PARTITION VALUE = 2011,
 PARTITION VALUE = 2012,
 PARTITION OTHERS), 
RANGE (FLDATE_MONTH) 
(PARTITION 01 <= VALUES < 07,
 PARTITION 07 <= VALUES < 13,
 PARTITION OTHERS);


Exercise 10. – Adding new RANGE partition on the first level into a multi-level-partitioned table (RANGE / RANGE, multi-level)

ALTER TABLE "SFLIGHT"."SBOOK_COPY" ADD PARTITION (FLDATE_YEAR) value = 1995;


Exercise +1. – Merge partitions (partitioned table to non-partitioned)

Merging the partitions results the table into the initial state (non-partitioned).

ALTER TABLE "SFLIGHT"."SBOOK" MERGE PARTITIONS


Conclusion

Partitioning/re-partitioning is an easy task in case of SAP HANA. The only important part is the syntax which you need to use correctly. Of course in a huge database the process will be time and resource consuming.

Task 2. – Documentation Library

IMPORTANT !
=================================================================

Where to find documentations in SAP Help Portal?

Which is the master SAP Knowledge Base Article of the SAP HANA partitioning?

  • 2044468 – FAQ: SAP HANA Partitioning

=================================================================

Before partitioning

Take into consideration that partitioning is not always necessary / not the only option (data deletion and / or archiving). Also housekeeping activity should be done before partitioning.

  • 2388483 – How-To: Data Management for Technical Tables
  • 2400024 – How-To: SAP HANA Administration and Monitoring
  • 2718597 – Collection solutions for some big growth tables relevant to HANA DB
  • 3348393 – Overview of the data management process in the context of SAP HANA
  • 2772795 – Recommendations to reduce the size of a table or partition where SAP HANA is the Database


Parameters

  • 2903141 – Best practice configuration checks for SAP HANA
  • 2874176 – Parameter Recommendations for Online Table Operations
  • 2600030 – Parameter Recommendations in SAP HANA Environments

Online/Offline parameters

  • 2530688 – Which HANA parameter needs to be configured online or offline
  • 2856122 – How to check if system restart required after modifying a parameter
  • 2186744 – FAQ: SAP HANA Parameters➡️16. Which parameters require a database restart to take effect?


Which best practices are available regarding SAP HANA partitioning?

  • 3199581 – Advantages of HANA Partitioning
  • 2289491 – Best Practices for Partitioning of Finance Tables
  • 2418299 – SAP HANA: Partitioning Best Practices / Examples for SAP Tables
  • 3146645 – What is the best approach in partitioning tables on SAP HANA?


Which other documentation is important regarding SAP HANA partitioning?

  • 3365898 – SAP HANA Partition Pruning
  • 3234063 – How to partition/re-partition a table
  • 2949496 – Add partition in HASH-RANGE partition
  • 3198529 – Supported combination of multi-level Partitioning
  • 3260918 – HANA: What are the main repartitioning phases?
  • 2938647 – How many levels partition are supported by HANA
  • 2444769 – Hana partitioning of a existing table with ROUND ROBIN
  • 2942131 – How to check the progress of an ongoing partitioning activity?
  • 3215774 – Monitor and troubleshoot for a long process of table repartition
  • 2902172 – How to remove or re-create SAP HANA Database table partition
  • 3215782 – How to perform Table partition or redistribution from HANA Cockpit
  • 3289845 – SAP systems restart requirements after SAP HANA table partitioning
  • 3288401 – How to find information about actual table partitioning in SAP HANA?
  • 3214124 – How to estimate how much extra space is needed for a table partition
  • 3111531 – SAP HANA – How to convert an existing hash partitioned table to range
  • 3029040 – Add a new range partition on the first level into a multi-level-partitioned table
  • 3356042 – Will partitioning help to reduce the disk/memory footprint of the table in SAP HANA?
  • 3314152 – Are there performance and resource related parameters for SAP HANA table partitioning?
  • 2698617 – How to increase the total number of partitions of an already hash partitioned table in SAP HANA
  • 2952826 – Perform table partitioning on HANA primary site requires additional steps on secondary site?
  • 3307500 – How to decide which partitioning type and column(s) should been used to partition a table in SAP HANA?
  • 3313721 – How to split/break a specific RANGE partition in already partitioned table which contains data in SAP HANA?
  • 3225157 – /hana/data & hana/log volumes consumption reaches critical size post re-partitioning operations on a large table


Which SAP HANA Alert IDs belongs to the partitioning topic?

  • 2445867 – How-To: Interpreting and Resolving SAP HANA Alerts
  • 1909763 – How to handle HANA Alert 17: ‘Record count of non-partitioned column-store tables’
  • 1910140 – How to Handle Alert 20 ‘Table growth of non-partitioned column-store tables’
  • 1910188 – How to handle HANA Alert 27: ‘Record count of column-store table partitions’


Which typical SAP HANA error belongs to the partitioning topic?

There is a typical error message: “maximum number of rows per partition reached for table” or “maximum number of rows per table or partition reached”.

  • 2212518 – HANA error maximum number of rows per partition reached
  • 3216228 – SQL error 2055 accessing : maximum number of rows per table or partition reached
  • 3284656 – Client Copy on SAP HANA fails with “maximum number of rows per table or partition reached” error
  • 3276680 – HANA Error “maximum number of rows per table or partition reached” occurs even though the record count is not near the 2 billion threshold limit


What are the available tools for partitioning?

The general way to do partitioning/re-partitioning is to use SQL commands. The action can be done in a graphical way also.


What are the known issues?

  • 3157330 – Memory allocation failed during (re-)partitioning
  • 2994267 – HANA DB partitioning is failing with out of memory error
  • 2890332 – Table re-partitioning failed with error “Data receive failed”
  • 3281773 – What cause the non uniform data distribution in HASH partitioned table in SAP HANA?
  • 3193321 – Partitioning or Re-Partitioning Job is running for a long time due to redo log contention that blocks Savepoint


What blog posts can be helpful?


Other articles in this series

✍️ Collected information about memory in context of SAP HANA

✍️ Collected information regarding High Availability and Disaster Recovery in SAP HANA

✍️ Collected information about reclaim / shrink / defragmentation topic in context of SAP HANA persistence (with example)


Other articles in connected series

✍️ Where can I find knowledge and information belongs to SAP HANA?

✍️ Where can I find information about the available tools for SAP HANA (all types of use)?


Useful bookmark

🔖 2872774 – Bookmark of Frequently Asked Questions for SAP HANA

🔖 3311408 – Bookmark of SQL Statement Collection reports for SAP HANA

🔖 2658020 – List of SAP HANA SQL Error Codes

Task 3. – Use Cases

Few examples about how documentations belongs to each other and how to use them in an effective way.

Case Steps
You want to partition a table.
(in general)
  1. You want to partition a table. 3146645 – What is the best approach in partitioning tables on SAP HANA?
  2. You want to check the recommendation by SAP. 2044468 – FAQ: SAP HANA Partitioning ➡️ 27. Are there specific partitioning recommendations for certain SAP applications and tables?
You want to re-partition a table.
  1. You want to check the actual table partition setup of the table. 3288401 – How to find information about actual table partitioning in SAP HANA?
  2. You want to check the actual table partition setup is following SAP recommendation. 2044468 – FAQ: SAP HANA Partitioning ➡️ 27. Are there specific partitioning recommendations for certain SAP applications and tables? If there is no exact recommendation: 3307500 – How to decide which partitioning type and column(s) should been used to partition a table in SAP HANA?
  3. You want to check the best practices before doing the re-partitioning activity. 2044468 – FAQ: SAP HANA Partitioning ➡️ 13. Which best practices exist for partitioning tables?
  4. You want to re-partition the table. 3234063 – How to partition/re-partition a table

Task 4. – Recommendation / Best Practice / Consideration

Few general information and recommendation which can be useful.

General
☝️It is a good practice to do data management or archive the data from the table, if the table grows into a significant size. In some case partitioning won’t be necessary.
☝️Before partitioning / re-partitioning housekeeping activity should be done to reduce the size of the table. This will reduce the partitioning time and resource needs.
☝️Doing partitioning / re-partitioning activity in the production environment is usually recommended when the system performance is moderated (so the system is not in the peak hours).
☝️Always a good practice to do the partitioning activity first in the test/development environment.
Resources
☝️Partitioning / re-partitioning is a resource intensive task
2044468 – FAQ: SAP HANA Partitioning ➡️ 43. What kind of resources are required during repartitioning?
☝️During the re-partitioning activity the recommendation of the available memory is 2x – 2.5x the memory of the table. 3215774 – Monitor and troubleshoot for a long process of table repartition
☝️During the re-partitioning activity the recommendation of the available disk size can be calculated with the formula. 3214124 – How to estimate how much extra space is needed for a table partition
Best Practices
2044468 – FAQ: SAP HANA Partitioning ➡️ 13. Which best practices exist for partitioning tables?

Do you have further questions?

Q&A link for SAP HANA: https://answers.sap.com/tags/73554900100700000996

Contribution

If you find any missing information belongs to the topic, please let me know. I am happy to add the new content. My intention is to maintain the content continuously to keep the info up-to-date.

Release Information

Release Date Description
2023.01.06 First/initial Release of the SAP Blog Post documentation (Technical Article).
2023.01.07 Section added: Before partitioning
2023.01.09 Section added: Which SAP HANA Alert IDs belongs to the partitioning topic?
2023.01.10 Section added: What are the available tools for partitioning?
2023.01.11 Reorganized the page structure based on Best Practices.
2023.03.01 Section added: Use Cases
2023.03.08 Section added: Table of Content
2023.03.17 Section added: Recommendation / Best Practice / Consideration
2023.06.27 Section added: Parameters
2023.07.25 Section added: Which typical SAP HANA error belongs to the partitioning topic?

 

Assigned Tags

      8 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Annelore Woithe
      Annelore Woithe

      Hello Laszlo,
      thanks a lot for this very good collection. Unfortunately I couldn't find an answer to my question. We are creating a table during system copy with SWPM. The tool adds to the part of partitioning a part "WITH PARAMETERS". It looks like this:

      CREATE COLUMN TABLE "<tablename>" (... )

      PARTITION BY HASH ("NR") PARTITIONS 2

      WITH PARAMETERS(

      'ESTIMATED_ROW_COUNT'= '1187168947'

      , 'PARTITION_SPEC' = 'HASH 3

      MANDT

      ,APLID

      ,NR

      ,MSGCNT'

      ) ;

      Running the statement on DB level with SQL is also not successful. Do you know something about usage of this option and where I can find any information about the correct syntax to use this option?

      Thanks in advance,
      Annelore

      Author's profile photo Laszlo Thoma
      Laszlo Thoma
      Blog Post Author

      Dear Annelore,

      There is no information about the exact error/error message related to the issue (Software Provisioning Manager).

      I did some search and found the following information:

      • 2044468 – FAQ: SAP HANA Partitioning
        - - > 31. What standard partitioning approach is used during migrations?
        - - > 35. Which errors can happen in relation to partitioning?
      • 2416490 - FAQ: SAP HANA Data Aging in SAP S/4HANA
        - - > 23. Is it possible to add a different partitioning on top of Data Aging?
        - - > 24. What re-partitioning possibilities exist for aging/TIME SELECTION partitioned tables?

      Please let me know if these documentation helps.

      Regards,

      Laszlo

      Author's profile photo Annelore Woithe
      Annelore Woithe

      Dear Laszlo,
      thanks for your quick reply. I looked into the mentioned SAP notes. It was not helpful. The source system is an ERP system on NW 7.40.

      The error message is the same when running the SQL statement on DB level:
      error%20message%20for%20table%20creation

      Thanks in advance,

      Annelore

      Author's profile photo Laszlo Thoma
      Laszlo Thoma
      Blog Post Author

      Dear Annelore,

      Based on the error message: "PARTITION_SPEC must be defined once" I have found:

      • 3285774 - Homogeneous system copy using SWMP: Import job terminates with SQL error 2048

      If the SAP Note does not help, please open a case with the next component: BC-DB-HDB-POR 

      Regards,

      Laszlo

      Author's profile photo IT BASISAdmin
      IT BASISAdmin

      Dear Laszlo,

       

      Thank you for your sharing。can you guide me how to test the performance after partition or re-partition HANA table than before 。(eg: S/4HANA ACDOCA  CDPOS large table。thanks。

       

      Best Regards,

      hayden

      Author's profile photo Laszlo Thoma
      Laszlo Thoma
      Blog Post Author

      Dear Hayden

      Partitioning can influence the performance in a positive (or negative) way:

      • 2044468 – FAQ: SAP HANA Partitioning - - > 6. Is partitioning transparent for the application?

      But the main reasons why partitioning is required:

      • 2044468 – FAQ: SAP HANA Partitioning - - > 7. For what reasons is partitioning required or useful?

      Recommendation belongs to ACDOCA and CDPOS tables can be find in SAP Knowledge Base Article:

      • 2044468 – FAQ: SAP HANA Partitioning - - > 27. Are there specific partitioning recommendations for certain SAP applications and tables?

      But also in Finance related SAP Notes:

      • 2289491 – Best Practices for Partitioning of Finance Tables
      • 2418299 – SAP HANA: Partitioning Best Practices / Examples for SAP Tables

      Please check:

      • 3307500 – How to decide which partitioning type and column(s) should been used to partition a table in SAP HANA?

      Example:

      • You have table ACDOCA or CDPOS with huge number of records.
        E.g. table not partitioned but records near the 2 billion record limit or records reached 2 billion record limit and the table already partitioned by HASH algorithm.
      • You can try to partition the table based on special recommendation belongs to Finance tables (RANGE partitioning).

      The performance improvement/degradation can be tested (from query perspective):

      • directly from database side (e.g. execution of a statement)
      • from application side

      But please keep in mind there are several other positive side could be of the partitioned table.

      Connected SAP Knowledge Base Article:

      • 3365898 - SAP HANA Partition Pruning

      Regards,

      Laszlo

      Author's profile photo ZiPeng Liu
      ZiPeng Liu

      Dear Laszlo,

      Thank your for your sharing。

      1、I have a SLT  system 。the target system ACDOCA  and source system ACDOCA  has not partitions。 due to source system can not stop。 i want to partition the target system ACDOCA table first 。if i do not paratition the source system ACDOCA now .whether is influence the extract data.

      2、Due we can not stop application 。so we need partitions online。whether will lock the ACDOCA table during the partitions until complete. and whether will long time influence the business .

      thank you for your guideness.

       

      Best Regards,

      Liu Zipeng

      Author's profile photo Laszlo Thoma
      Laszlo Thoma
      Blog Post Author

      Dear Liu ZiPeng,

      Please check SAP Landscape Transformation related SAP Knowledge Base Articles:

      • 2990609 - Important KBAs and SAP Notes - SLT
      • 2014562 - FAQ: SAP HANA LT Replication Server (SLT)
      • 2209687 - SLT HANA target table Source Table Has More Than 2 Billion Records
      • 2528241 - Partitioning of tables replicated by SLT to SAP HANA database

      Regarding online partitioning and lock related question, please find documentations:

      • 2874176 - Parameter Recommendations for Online Table Operations
      • 2044468 - FAQ: SAP HANA Partitioning - - > 12. Are locks involved when a table is partitioned?
      • 3193321 - Partitioning or Re-Partitioning Job is running for a long time due to redo log contention that blocks Savepoint

      If you need more support with SAP Landscape Transformation, please open a support case with the next component: CA-LT-SLT

      Regards,

      Laszlo