Database table re-partitioning using trigger-based capture and replay

Patent metadata

Field	Value
Publication number	US-10055440-B2
Application number	US-201414188543-A
Country	US
Kind code	B2
Filing date	Feb 24, 2014
Priority date	Feb 24, 2014
Publication date	Aug 21, 2018
Grant date	Aug 21, 2018

Abstract

Partitioning of a source table of a database to a target table is initiated. Thereafter, a replay table is generated that is populated with triggers for database operations performed on the source table for subsequent replay for the target partitions. Data is later moved (e.g., asynchronously moved, etc.) from the source table to the target table. The database operations are replayed on the target table T subsequent to the moving of the data using the replay table. In addition, the source table is dropped when all of the data has been moved to the target table and there are no operations requiring replay. Related apparatus, systems, techniques and articles are also described.

First claim

What is claimed is:

1. A method comprising:
initiating a repartitioning of a source table of a database, the repartitioning comprising moving data from the source table to a target table;
concurrently performing database operations on the source table and moving the data from the source table to the target table, the database operations modifying one or more rows in the source table;
generating, based at least on the database operations performed on the source table, a replay table, the replay table storing, in response to triggers, one or more key values corresponding to the one or more rows in the source table that have been modified by the database operations, each of the one or more key values being associated with an insert operation or a delete operation, the replaying table comprising a key column for storing the one or more key values corresponding to the one or more rows in the source table that have been modified by the database operation, and the generation of the replay table comprising:
responding to an update operation on a first row in the source table by storing, in the replay table, a corresponding insert operation and delete operation, the corresponding insert operation and delete operation being stored in the replay table instead of the update operation, and the storage of corresponding insert operation and delete operation comprising a first key value associated with an insert operation type indicator and a second key value associated with a delete operation type indicator, the first key value comprising the original key and/or a new key of the first row modified by the update operation, and the second key value comprising an original key of the first row modified by the update operation;
replaying, on the target table, the database operations in the replay table, the database operations in the replay table including only insert operations and/or delete operations for inserting and/or deleting the one or more rows from the target table, the replaying of the database operations being performed subsequent to the moving of the data from the source table to the target table, the replaying of the database operations comprising performing an inner join of the replay table with the source table to at least retrieve, from the source table, the one or more rows in the source table that have been modified by the database operations; and
dropping the source table when all of the data has been moved to the target table and there are no more database operations requiring replay.

2. The method as in claim 1 , wherein the generation of the replay table further comprises responding to an insert operation by storing, in the replay table, a third key value associated with the insert operation type indicator, and wherein the third key value is associated with a second row that is inserted into the source table by the insert operation.

3. The method as in claim 1 , wherein the generation of the replay table further comprises responding to a delete operation by storing, in the replay table, a third key value associated with the delete operation type indicator, and wherein the third key value is associated with a second row that is deleted from the source table by the delete operation.

4. The method as in claim 1 , further comprising: exclusively locking the source tables while replaying the database operations using the replay table.

5. The method as in claim 1 , further comprising:
renaming a name of the target table to a name of the source table upon dropping the source table.

6. A non-transitory computer program product storing instructions which, when executed by at least one data processor forming part of at least one computing system, results in operations comprising:
initiating a repartitioning of a source table of a database, the repartitioning comprising moving data from the source table to a target table;
concurrently performing database operations on the source table and moving the data from the source table to the target table, the database operations modifying one or more rows in the source table;
generating, based at least on the database operations performed on the source table, a replay table, the replay table storing, in response to triggers, one or more key values corresponding to the one or more rows in the source table that have been modified by the database operations, each of the one or more key values being associated with an insert operation or a delete operation, the replaying table comprising a key column for storing the one or more key values corresponding to the one or more rows in the source table that have been modified by the database operation, and the generation of the replay table comprising:
responding to an update operation on a first row in the source table by storing, in the replay table, a corresponding insert operation and delete operation, the corresponding insert operation and delete operation being stored in the replay table instead of the update operation, and the storage of corresponding insert operation and delete operation comprising a first key value associated with an insert operation type indicator and a second key value associated with a delete operation type indicator, the first key value comprising the original key and/or a new key of the first row modified by the update operation, and the second key value comprising an original key of the first row modified by the update operation;
replaying, on the target table, the database operations in the replay table, the database operations in the replay table including only insert operations and/or delete operations for inserting and/or deleting the one or more rows from the target table, the replaying of the database operations being performed subsequent to the moving of the data from the source table to the target table, the replaying of the database operations comprising performing an inner join of the replay table with the source table to at least retrieve, from the source table, the one or more rows in the source table that have been modified by the database operations; and
dropping the source table when all of the data has been moved to the target table and there are no more database operations requiring replay.

7. The computer program product as in claim 6 , wherein the generation of the replay table further comprises responding to an insert operation by storing, in the replay table, a third key value associated with the insert operation type indicator, and wherein the third key value is associated with a second row that is inserted into the source table by the insert operation.

8. The computer program product as in claim 6 , wherein the generation of the replay table further comprises responding to a delete operation by storing, in the replay table, a third key value associated with the delete operation type indicator, and wherein the third key value is associated with a second row that is deleted from the source table by the delete operation.

9. The computer program product as in claim 6 , wherein the operations further comprise:
exclusively locking the source tables while replaying the database operations using the replay table.

10. The computer program product as in claim 6 , wherein the operations further comprise:
renaming a name of the target table to a name of the source table upon dropping the source table.

11. A system comprising:
at least one data processor; and
memory storing instructions which, when executed by the at least one data processor, results in operations comprising:
initiating a repartitioning of a source table of a database, the repartitioning comprising moving data from the source table to a target table;
concurrently performing database operations on the source ta

Assignees

• Bensberg Christian
• Faerber Franz
• Fricke Lars
• Lee Juchang
• Sedler Roland
• Tonder Andreas
• Ziegler Emanuel
• Sap Se

Inventors

• Bensberg Christian
• Faerber Franz
• Fricke Lars
• Lee Juchang
• Sedler Roland
• Tonder Andreas
• Ziegler Emanuel

Classifications

• G06F16/2282 Primary

  Tablespace storage structures; Management thereof · CPC title

• G06F17/30339 Primary

  Physics · mapped topic