HANA DB - copy large tables with high performance
by Claudia Leiders
In a current customer project there was a requirement to migrate a table with an extremely large number of entries into a new namespace. Since it is not possible to rename SAP tables, the only solution was to copy them. However, with a table containing 1.8 billion entries, this is a real challenge in terms of both runtime and execution itself.
In the end, only the following approach worked:
Copy directly on the HANA DB
Free memory check
First, it is necessary to evaluate the size of the table to be copied (Transaction DBACOCKPIT -> Diagnostics -> Tables/Views) and determine the available free memory (Transaction DBACOCKPIT -> Current Status -> Overview).
If enough storage capacity is available, the table can now be copied with the following statement, either with an SQL statement in Hana-Studio or with "EXEC SQL" in ABAP:
Use the following statement to copy the table: CREATE (COLUMN) TABLE "/<Namespace1>/<Table name>" LIKE "/<Namespace1>/<Table name>" WITH DATA
Eine Doku finden Sie auf https://help.sap.com/viewer/4fe29514fd584807ac9f2a04f6754767/2.0.03/en-US/20d58a5f75191014b2fe92141b7df228.html#loio20d58a5f75191014b2fe92141b7df228__create_table_like_clause (Stand: 12.10.2020)
Follow up ABAP Dictionary
Since the copied table now exists in the database but not in the ABAP Dictionary, the table must now be copied (as usual) in SE11. Now the copied table is also visible in SAP (including data). Further processing (e.g. SE14) is not necessary.
The copy of a table with 1.8 billion lines and a total column width of 781 characters took about 6h.