on 03-10-2024 7:55 AM
What is Hana Partitioning?
The partitioning feature of the SAP HANA database splits column-store tables horizontally into disjunctive sub-tables or partitions. In this way, large tables can be broken down into smaller, more manageable parts. Partitioning is typically used in multiple-host systems, but it may also be beneficial in single-host systems. Typical advantages of partitioning are e.g. partition pruning and an improved performance of the delta merge operation.
Furthermore, keep in mind that execution of the partitioning can take a long time, has a relatively high memory consumption and requires an exclusive lock (only selects are allowed).
But with the right partitioning which fits to the where clause of a SQL statement, only a single partition has to be accessed in best case. And the result is a better query performance and a faster and leaner delta merge operation
Below is example for CE11000 table :
As a pre-requisite need to check below parameter : SML - statement Memory Limit
1) In our case it was set to 100GB.
SML was changed to 300GB
2) we need to set log_mode to overwrite & enable_auto_log_backup to no
Now go to LTRC - Respective schema -> Suspend CE11000 table
Add Partition command under -> LTRS -> Advance Replication settings -> Table settings ->
PARTITION BY HASH (BELNR) PARTITIONS 8 , as shown below and save
Now login into HANA Studio which has SYSTEM ID roles to run Alter query
Thread Details :
Query took ~53 minutes
Post completion:
SML was changed back to 100GB post completion. As a best practice SML should be 2*Table_HANA_MEMEORY + 50GB(Delta working space).
Resume the replication of CE11000 post completion of online partition to clear off all logging table entries.
Refer below OSS notes :
2044468 - FAQ: SAP HANA Partitioning
2418299 - SAP HANA: Partitioning Best Practices / Examples for SAP Tables
Once the activity is completed kindly revert the below changes which was made before performing online partitioning - step 2
Thanks
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
75 | |
10 | |
10 | |
7 | |
7 | |
6 | |
6 | |
6 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.