Single-Operation Schema Evolution via Ghost Table and Atomic Transform
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current database systems face limitations in schema evolution, requiring multiple operations, potential downtime, and restrictive change types, while maintaining data consistency and availability, especially when dealing with large and complex data structures.
Innovation Solution
The TRANSFORM command enables flexible schema evolution by allowing users to redefine data structures through full or differential object syntax, maintaining data and metadata consistency, and supporting online operations, with features like ghost objects, validation, and exception handling to manage incompatible data records.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Adaptability or versatility
If current database functionality is used for schema evolution, then data structure changes can be implemented, but multiple operations are required and data must be taken off-line
Solution Approach 1:
The patent combines multiple schema evolution operations into a single integrated command. The TRANSFORM command consolidates what were previously separate operations (CREATE, DROP, ALTER, data transformation) into one atomic operation that can be executed in a single statement, eliminating the need for multiple sequential commands and reducing operational complexity.
Solution Approach 2:
The TRANSFORM command serves multiple functions simultaneously: it creates new tables, transforms data, updates metadata, and maintains indexes all in one operation. This multi-functional approach replaces the need for multiple specialized commands, making the schema evolution process more efficient and easier to operate.
2Adaptability or versatility
If current database functionality is used for schema evolution, then data structure changes can be implemented, but the data warehouse must be taken off-line
Solution Approach 1:
The system performs preliminary actions by creating a ghost table with the new schema definition before the actual transformation. This ghost table is prepared in advance with the target structure, allowing the transformation to proceed without taking the data warehouse off-line, as the new structure is ready to receive data immediately.
Solution Approach 2:
The ghost table acts as an intermediary during the schema evolution process. It serves as a temporary storage structure that receives transformed data while the original table remains accessible for queries. This intermediary mechanism enables online schema evolution by decoupling the transformation process from table accessibility.
3Adaptability or versatility
If schema evolution is performed with current functionality, then data structures can change, but data consistency and integrity are difficult to maintain
Solution Approach 1:
The system implements feedback mechanisms through validation rules that check data consistency during transformation. The TRANSFORM command includes built-in validation that verifies data integrity constraints are met during the evolution process, providing immediate feedback on any consistency issues and preventing transformations that would compromise data integrity.
Solution Approach 2:
The system prepares cushioning measures by creating the ghost table with the complete target schema including all constraints, indexes, and validation rules before the transformation begins. This beforehand preparation ensures that the target structure is ready to enforce data consistency from the moment transformation starts, preventing integrity violations.
4Adaptability or versatility
If schema evolution is performed with current functionality, then data structures can change, but untransformed data remains affecting query performance
Solution Approach 1:
The transformation process maintains continuity by continuously transforming and inserting data into the ghost table while the original table remains operational. Unlike batch transformations that stop query access, this continuous transformation approach ensures that data is progressively converted without creating large volumes of untransformed data that would degrade query performance.
Solution Approach 2:
The system extracts transformed data directly into the ghost table during the transformation process itself, rather than leaving data in the original table format. This extraction of transformed data into the new structure eliminates the performance penalty of having mixed transformed and untransformed data, as queries can immediately utilize the properly formatted data in the ghost table.
Data Source
AI summary
A method for modifying an original data object in a database using a single command is disclosed. The single command is first validated. Once the command has been validated a ghost data object is created using the new definition. The ghost data object is populated with data from the original object. The transaction log records from the original data object are applied to the ghost data object. The original data object is then locked for exclusive access. The final transaction log records from the original data object are applied to the ghost data object. In the final step the database switches to the new data object. Dependent objects are automatically transformed as part of the same operation.