Re-partitioning a table using new empty clone table and copy back feature

RE-PARTITIONING HANA COLUMN TABLE USING COPY,REPLACE and MINUS functionality

Here I wish to discuss about how we performed a partition of table by using an empty clone of the existing table and later had synced both the table to overcome couple of performance related constrain in our production hana db.

We were trying to re-partition a huge table and it was failing continuously after few hours despite changing couple of idle_cursor_lifetime, network parameter ,user specific statement_memory_limit etc. Hence we were looking for workarounds and below is how I was able to overcome the challenge.

AIM: Re-partition the table SAPSSS.T1

Steps:

1.We created an empty table with name SAPSSS.T1_new with the help of create statement structure from SAPSSS.T1.

CALL GET_OBJECT_DEFINITION(‘SAPSSS’, T1′);

Copy the sql definition from above in notepad and replace T1 with T1_new and execute it in SQL editor in HANA DB

2.Re-partition this SAPSSS.T1_new (empty table)

ALTER TABLE SAPSSS.T1_new PARTITION BY HASH (DOCNUM) PARTITIONS 7;

3.Insert records from SAPSSS.T1 to SAPSSS.T1_new using below command.

insert into SAPSSS.T1_new select * from SAPSSS.T1;

This will automatically distribute the table rows based on the partition that we performed in step 2.

4.Now rename the tables