Sequencing Temporal Table Rows for Error-Free Updates
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Optimizing UPDATE or DELETE statements in SQL for temporal tables, where multiple source rows affect the same target row, is challenging due to the complexity of managing non-overlapping validity periods.
Innovation Solution
A method is introduced to sequence requests for temporal Source tables applied to temporal Target tables, ordering rows by the join column and period-to-be-modified, and creating LDIFF and RDIFF rows to manage non-overlapping ValidTime ranges, allowing for efficient updates or deletes without errors.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If multiple source rows are applied to a single target row in a temporal table, then the completeness of data updates is improved, but the complexity of managing non-overlapping validity periods increases
Solution Approach 1:
The patent segments the validity period management by creating LDIFF (Left Difference) and RDIFF (Right Difference) rows that separate the temporal ranges. When multiple source rows affect the same target row, the system divides the target row's validity period into distinct segments, each associated with a specific source row, ensuring non-overlapping temporal ranges while maintaining complete data updates.
Solution Approach 2:
The patent applies preliminary action by ordering source rows by their ValidTime dimension before applying them to the target row. This pre-ordering ensures that the system processes source rows in the correct temporal sequence, preventing conflicts and simplifying the management of non-overlapping validity periods during the update operation.
2Manufacturing precision
If source rows are ordered by join column and period-to-be-modified, then the accuracy of temporal modifications is improved, but the processing time increases
Solution Approach 1:
The patent performs preliminary ordering of source rows by join column and period-to-be-modified before the actual update operation. This pre-sorting ensures accurate temporal modifications without requiring complex sorting during the update process itself, balancing precision with processing efficiency.
Solution Approach 2:
The system uses the inherent ordering properties of the source rows (by ValidTime) to self-organize the update sequence. By leveraging the natural temporal ordering of the data, the system achieves accurate temporal modifications without requiring additional expensive sorting operations during processing.
3Productivity
If TransactionTime is truncated and ValidTime ranges are adjusted, then the efficiency of data modification is improved, but the complexity of temporal range management increases
Solution Approach 1:
The patent segments temporal range management into standardized LDIFF and RDIFF operations. By truncating TransactionTime and adjusting ValidTime ranges through these predefined segmentation operations, the system improves modification efficiency while containing complexity through reusable, standardized temporal range handling mechanisms.
Data Source
AI summary
A SEQUENCED request a) to apply a temporal Source table to a temporal Target table under a predicate is received. The Source table includes a plurality of rows that qualify to apply to a single Target table row under the predicate. The predicate specifies a Source table join column. Each of the plurality of Source table rows and the single Target table row include a ValidTime dimension. The plurality of Source table rows is b) ordered by the Source table join column and a period-to-be-modified. The next of the ordered plurality of Source table rows is c) applied by determining that the Target table row has a TransactionTime and, in response, closing out the TransactionTime of the Target table row. Applying the next of the ordered plurality of Source table rows includes identifying an RDIFF range as the period-to-be-modified of the Source table row being applied that is later than the ValidTime range of the single Target table row. Applying the next of the ordered plurality of Source table rows includes creating an RDIFF row being the same as the Target table row except the ValidTime of the RDIFF row is set to RDIFF range. A second of the ordered plurality of Source table rows d) is applied by applying c) to the RDIFF row as the Target table row.