HANA DB - copying large tables with high performance
In a current customer project, there was a requirement to migrate a table with an extremely large number of entries to a new namespace. Since renaming SAP tables is not possible, the only option was copying. However, for a table with 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 to 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 to determine the available free memory (Transaction DBACOCKPIT -> Current Status -> Overview).
If enough memory is available, the following statement can now be used to copy the table, either via an SQL statement in Hana Studio or via "EXEC SQL" in ABAP:
Use the following statement to copy the table: CREATE (COLUMN) TABLE "/<namespace1>/<table name>" LIKE "/<namespace1>/<table name>" WITH DATA
A documentation you can find here https://help.sap.com/viewer/4fe29514fd584807ac9f2a04f6754767/2.0.03/en-US/20d58a5f75191014b2fe92141b7df228.html#loio20d58a5f75191014b2fe92141b7df228__create_table_like_clause (from: 12.10.2020)
Copy ABAP Dictionary
Since the copied table now exists in the database but not in the ABAP Dictionary, the table must now still be copied (as usual) in SE11. Now the copied table is also visible in SAP (incl. data). Further processing (e.g. SE14) is not necessary.
The copy of a table with 1.8 billion rows and a total column width of 781 characters took about 6h.