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

Collected information regarding partitioning in SAP HANA (with examples)

last updated: 2023-01-11

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.

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. – 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 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 +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
  • 2772795 – Recommendations to reduce the size of a table or partition where SAP HANA is the Database

Which best practices are available regarding SAP HANA partitioning?

  • 3199581 – Advantages of HANA Partitioning
  • 2289491 – Best Practices for Partitioning of Finance Tables
  • 2874176 – Parameter Recommendations for Online Table Operations
  • 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?

  • 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
  • 3288401 – How to find information about actual table partitioning in SAP HANA?
  • 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
  • 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?

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’

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:

Do you have further questions?

Q&A link for SAP HANA, express edition: https://answers.sap.com/tags/73555000100800000651

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.

 

Assigned Tags

      Be the first to leave a comment
      You must be Logged on to comment or reply to a post.