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

[rajarajeswari_kaliyaperum]

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

rename table SAPSSS.T1 to SAPSSS.T1_original;

rename table SAPSSS.T1_new to SAPSSS.T1;

5.Now we have to ensure that the current tables :SAPSSS.T1 and SAPSSS.T1_original has same number of records

6.If we see a record count mismatch, then we need to perform a delta insert into SAPSSS.T1 from SAPSSS.T1_original

insert into SAPSSS.T1 select * from SAPSSS.T1_original MINUS select * from SAPSSS.T1;

Note: If this operation also has issues in production environment causing heavy load, then we have an option of first exporting this table to any other non-production system and then performing all these activities there and then import it back.

As a general DB advise, do not try this out directly in production without testing it in lower environment .

How to determine and perform partition in HANA DB refer below blog.
https://blogs.sap.com/2020/09/11/how-to-determine-sap-hana-partitioning-algorithm/

For export and import steps pelase refer below blog.

https://blogs.sap.com/2020/09/12/how-to-export-and-import-table-from-hana-db/?update=updated

Thanks for reading!

Please click on like if you found this useful and for more such posts follow me at rajarajeswari_kaliyaperumal

Please leave your comments and suggestion here!