Sap Hana Online Table Partitioning - Example

[VishuKumarKura]

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

[VishuKumarKura]

Once the activity is completed kindly revert the below changes which was made before performing online partitioning - step 2

Thanks