last updated: 2024-02-01
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 (The internal links had been removed cause the new blogging platform only supports basic html, and the use of anchor links not supported.)
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?
=================================================================
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.
Parameters
Online/Offline parameters
Which best practices are available regarding SAP HANA partitioning?
Which other documentation is important regarding SAP HANA partitioning?
Which SAP HANA Alert IDs belongs to the partitioning topic?
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".
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?
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 persiste...
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) | |
You want to 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? |
2024.02.01 | SAP Community 2024 optimized content. |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
20 | |
9 | |
8 | |
7 | |
7 | |
7 | |
7 | |
6 | |
5 | |
5 |