Single-Scan Pipeline Merge for Multi-Table Data Integration
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
In data warehouse environments, performing multi-table merge operations is resource-intensive due to the need for multiple scans of large or complex source data structures, leading to significant computational resource consumption.
Innovation Solution
A method that allows for multiple merge operations to be performed using only a single scan of the source data structure, where the source data is combined with multiple destination data structures, and subsequent merge operations are augmented using intermediate data structures, enabling concurrent processing of different portions of the source data stream across multiple destination tables.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If multiple MERGE operations are performed sequentially with separate source scans for each operation, then each MERGE operation can be completed independently, but the computational resources are consumed significantly and the processing time is lengthy
Solution Approach 1:
The patent combines multiple separate source scans into a single source scan that feeds data to multiple destination tables simultaneously. The single scan operation reads the source table once and distributes the data to multiple destinations through concurrent MERGE operations, eliminating the need for repeated scans and reducing overall processing time while maintaining data accuracy.
Solution Approach 2:
The patent implements continuous processing by maintaining an open cursor on the source table that continuously feeds data rows to multiple destination tables without interruption. The system processes data in a continuous stream, allowing multiple MERGE operations to proceed concurrently without waiting for each other to complete, thereby maximizing resource utilization and processing efficiency.
2Productivity
If a single source scan is used for multiple MERGE operations, then processing time and computational resources are conserved, but the system complexity increases due to concurrent operations
Solution Approach 1:
The patent introduces an intermediary cursor mechanism that manages the single source scan and distributes data to multiple destination tables. This cursor acts as a mediator between the source table and multiple destinations, coordinating the concurrent MERGE operations and managing data flow without requiring complex inter-operation communication, thereby simplifying the overall system architecture.
Solution Approach 2:
The patent segments the processing workflow into independent concurrent MERGE operations that can execute in parallel. Each MERGE operation is treated as a separate task that independently processes data from the shared source scan, allowing the system to divide the complex multi-table update task into manageable, non-blocking segments that can be executed simultaneously.
Data Source
AI summary
A method for performing multiple merge operations with a source data structure and a plurality of destination data structures, in which the source data structure is scanned one time in order to obtain source data that is then used for multiple merge operations. The source data is then used consecutively in order to merge the source data into the one or more destination data structures. Each merge is performed using the same scan of the source data structure and within the same execution of a database command.