Database Table Reorganization via Synchronous Data Replication
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing methods for reorganizing or moving database tables often result in limited accessibility, require staging areas that consume resources, and can cause system downtime due to the need for multiple replay iterations and locking of the source table, leading to data inconsistencies and delays.
Innovation Solution
A system comprising a table generator module, a table updater module, and a data replicator module that synchronously updates and replicates data between a source and target table, eliminating the need for a staging area and reducing downtime by allowing real-time data synchronization and replication.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If a staging area is used to track modifications during table reorganization, then data consistency between source and target tables is maintained, but disk or memory space is consumed and the process requires multiple replay iterations causing extended downtime
Solution Approach 1:
The patent extracts the modification tracking mechanism from a separate staging area and integrates it directly into the target table structure. By storing modification information within the target table itself rather than in an external staging area, the system eliminates the need for additional disk or memory space while maintaining data consistency through the same tracking functionality.
Solution Approach 2:
The patent merges the modification tracking functionality with the target table by incorporating a modification indicator column directly into the target table structure. This consolidation combines the data storage function and the modification tracking function into a single integrated structure, eliminating the need for a separate staging area and reducing resource consumption.
2Reliability
If the source table is locked during the replay process to ensure data consistency, then data integrity is maintained, but system accessibility is limited and downtime is extended
Solution Approach 1:
The patent performs preliminary actions by creating the target table with the modified structure and populating it with data before the source table is locked. The modification tracking mechanism is set up in advance, allowing the system to switch to the target table without requiring a lock during the replay process, thus maintaining system accessibility while ensuring data integrity.
Solution Approach 2:
The patent implements a dynamic switching mechanism where the system can transition from the source table to the target table without locking. The modification indicator in the target table dynamically tracks changes, allowing the source table to remain accessible while modifications are being applied, thereby maintaining both data integrity and system accessibility simultaneously.
3Reliability
If multiple replay iterations are performed to apply modifications to the target table, then complete data synchronization is achieved, but the reorganization process time is extended causing system downtime
Solution Approach 1:
The patent implements a feedback mechanism through the modification indicator column in the target table, which provides immediate information about the synchronization status between source and target tables. This feedback allows the system to determine when replay is complete without requiring multiple iterative checks, significantly reducing the reorganization process time while ensuring complete data synchronization.
Solution Approach 2:
The patent enables the system to skip unnecessary replay iterations by using the modification indicator to immediately identify when all modifications have been applied. Instead of performing multiple fixed iterations, the system rushes through the replay process by stopping as soon as synchronization is confirmed, thereby minimizing reorganization time while maintaining data synchronization integrity.
4Adaptability or versatility
If the source table structure is modified to add or drop columns, then the table meets new requirements, but the reorganization process becomes more complex and time-consuming
Solution Approach 1:
The patent segments the reorganization process into distinct phases: creating the target table with the new structure, copying data with modification tracking, and switching tables. By segmenting the process, complex structural modifications such as adding or dropping columns can be applied to the target table independently without affecting the source table, reducing overall process complexity while maintaining structural flexibility.
Solution Approach 2:
The patent performs preliminary structural modifications on the target table before data migration begins. By pre-configuring the target table with the desired structure (adding or dropping columns as needed), the system simplifies the reorganization process because the structural changes are already in place and do not need to be applied during the complex data migration phase, reducing both complexity and time requirements.
Data Source
AI summary
A computer system including instructions recorded on a non-transitory computer-readable storage medium and readable by at least one processor may include a table generator module, a table updater module, and a data replicator module. The table generator module may be configured to cause the at least one processor to generate a target table. The table updater module may be configured to cause the at least one processor to modify data in the target table synchronously with data being modified in a source table. The data replicator module may be configured to cause the at least one processor to replicate the data contained in the source table in the target table.