Hana table partitioning is one of major problems for most of the customers to re partition of HANA tables due to the reason that a table partition cannot contain more than 2,147,483,648 (2 billion) rows. During the time customer need to perform table re partitioning due to 2 billion limit and For large tables – table partitioning takes lot of time and customer wants to optimize overall run-time of table partitioning .
Here i am providing tips to improve total run-time of Hana table partitioning along with table partitioning steps:
- Run delta merge on table before re-partitioning . Command for delta merge “MERGE DELTA OF <Table Name> FORCE REBUILD;”
- Load full table in Memory, command “LOAD <Table Name> ALL;”
- Set Split Threads to maximum number based on available CPUs ( if sufficient resources are available) – indexserver.ini -> [partitioning] -> split_threads (default: 16) – (It can range between 1 and 128) . It directly improves the runtime.
Number of CPU threads your system has by executing below query.
select * from m_host_information where key= ‘cpu_threads’;
- Execute re-partitioning.
Syntax for table re-partitioning using HASH Key:
alter table “<schema>”.”<tablename>” partition by hash (<column1>,<column2>,<…>) partitions <desired number of partitions>;
