Hana table partitioning | re-partitioning | Improve Performance

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:

  1. Run delta merge on table before re-partitioning . Command for delta merge “MERGE DELTA OF <Table Name> FORCE REBUILD;”
  2. Load full table  in Memory, command “LOAD <Table Name> ALL;”
  3. 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>;

Leave a Reply

Your email address will not be published. Required fields are marked *