Database Column Datatype Migration via Dual-Column Segmentation
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing methods for altering the datatype of a column in a relational table are inefficient, particularly when migrating data from an original datatype to a new datatype, as they often require bulk data migration, which can be time-consuming and cause storage issues like row-chaining and non-locality, especially in large tables with millions of rows.
Innovation Solution
The solution involves creating a new column with the desired datatype while retaining the old column, allowing data items to remain in the original column until updated, and handling data manipulation language (DML) operations to gradually migrate data to the new column, with scheduled migration plans to minimize overhead and optimize storage.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Adaptability or versatility
If bulk migration of data items is performed using Recursive Procedure Invocation, then the column datatype can be changed, but the migration process takes an unacceptably long time and causes row-chaining and non-locality of data storage
Solution Approach 1:
The patent segments the data migration process by creating a new column with the desired datatype and leaving the original column in place. Data migration is divided into two phases: (1) structural change phase where only the column definition is altered, and (2) data migration phase where data is gradually moved to the new column. This segmentation allows the schema to be updated immediately without waiting for bulk data migration, significantly reducing the time loss associated with traditional recursive procedure invocation methods.
Solution Approach 2:
The patent applies preliminary action by creating the new column with the correct datatype and storage format before migrating the data. The new column is prepared in advance with appropriate storage allocation, and the migration process simply involves moving existing data to this pre-prepared column. This eliminates the need to wait for storage allocation during the migration process itself, reducing overall migration time.
2Manufacturing precision
If a new column is created with the appropriate storage for the new datatype, then the storage format issue is resolved, but the original column with old datatype data must be converted and migrated, which is time-consuming
Solution Approach 1:
The patent separates the schema modification operation from the data migration operation. The schema change (creating the new column with correct storage format) is performed first and immediately, while the data migration is handled separately through gradual updates. This segmentation allows the system to achieve correct storage format without being blocked by the time-consuming data conversion process.
Solution Approach 2:
The patent introduces dynamic handling of column data through triggers that automatically migrate data from the old column to the new column as rows are updated. This dynamic approach allows the system to maintain both the old and new column structures temporarily, with data gradually shifting to the new format as needed, rather than requiring a complete bulk migration to complete the datatype change.
3Object-affected harmful factors
If the original column is replaced with a new column, then the storage issues are eliminated, but the data conversion and migration process causes row-chaining and non-locality of data storage
Solution Approach 1:
The patent uses the new column as an intermediary structure that bridges the old column and the desired final state. The new column serves as a temporary holding structure with the correct storage format, and data is gradually migrated from the old column to this intermediary. This intermediary approach allows the system to maintain storage efficiency while avoiding the complexity of immediate complete replacement, as the old column can remain in place until data migration is complete.
Solution Approach 2:
The patent ensures continuity of useful action by maintaining both the old and new column structures during the transition period. The system continues to accept and process data in the old format while simultaneously migrating to the new format. This continuous approach avoids the disruption and complexity of complete replacement, allowing data operations to continue uninterrupted while storage format is gradually improved.
Data Source
AI summary
Techniques are described herein for altering the datatype of a column without having to immediately migrate the data items that currently reside in the column. In one embodiment, the alteration is performed without migration by creating a new column having the desired datatype while retaining the old column. Instead of migrating the data items from to the new column at the time the column is altered, the items remain in the original column until the items are updated.