Database Column Dictionary Type Conversion
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Altering the data type of a column in a database is computationally intensive and results in extended downtime, making the database unavailable to users, as existing methods require exclusive access and involve costly computation and storage expenses.
Innovation Solution
Converting the original dictionary associated with a column into a new dictionary with a different data type, reducing computation costs and minimizing downtime by using column-based storage and tokenization for efficient data compression.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If the data type of a column is altered using traditional methods, then data compression and analysis efficiency are improved, but computation cost increases and database downtime increases
Solution Approach 1:
The patent segments the dictionary into multiple partitions that can be converted independently and in parallel. Instead of converting the entire dictionary at once (which causes full database downtime), the conversion is divided into smaller chunks that can be processed separately, reducing the impact on database availability.
Solution Approach 2:
The patent performs preliminary actions by creating a new dictionary in the target data type alongside the existing dictionary, then gradually migrating data between them. This allows the system to prepare the new dictionary structure beforehand and switch to it once ready, rather than performing the entire conversion during a single downtime period.
2Productivity
If the data type of a column is altered using traditional methods, then data compression and analysis efficiency are improved, but computation cost increases
Solution Approach 1:
The dictionary conversion is segmented into multiple smaller conversion tasks that can be executed in parallel across different processing units. This distribution of computation reduces the peak computational load and allows for more efficient utilization of available computing resources.
Solution Approach 2:
The patent creates a copy of the dictionary in the new data type rather than transforming the original dictionary in place. This copying approach allows the original dictionary to remain intact and operational while the new dictionary is being populated, enabling parallel processing and reducing computation bottlenecks.
3Reliability
If exclusive access to a table is required for data type alteration, then data integrity is maintained, but database availability to users decreases
Solution Approach 1:
The patent segments the dictionary into multiple partitions that can be converted independently. This segmentation allows different portions of the dictionary to be accessed and converted simultaneously by different processes, maintaining data integrity through controlled access while improving overall availability through parallel operations.
Solution Approach 2:
The patent implements a dynamic conversion process where the dictionary is gradually transformed from the old data type to the new data type while the database remains operational. The system dynamically manages the transition between old and new dictionary versions, allowing users to access data during the conversion process rather than requiring a static, all-or-nothing approach.
Data Source
AI summary
Disclosed herein are system, method, and computer program product embodiments for altering the data type of a column in a database. An embodiment operates by converting an original dictionary associated with a column into a new dictionary. The new dictionary stores the values of the original dictionary using a different data type. An index vector containing the keys of the original dictionary is then updated to contain the associated keys of the new dictionary. Because the size of the original dictionary is often substantially smaller than the number of rows in the associated column, this dictionary conversion decreases the computation cost to the database system of altering the data type of the column and reduces or even minimizes database downtime for users.