Materialized View Update via Local Change Markers
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing data store technologies face performance issues when updating materialized views across multiple joined tables, as they require complex triggers and additional tables to maintain updated views, leading to inefficient and time-consuming updates, especially when only a few rows have changed.
Innovation Solution
The mechanism uses local markers to track changes in individual base tables, allowing for efficient updates to materialized views by identifying and processing only the rows that have changed, rather than re-processing the entire view, thereby minimizing complexity and increasing speed.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If materialized views are updated using traditional methods with triggers and additional tables, then the views can maintain updated data, but the update process becomes complex and time-consuming
Solution Approach 1:
The patent divides the update mechanism into separate components: base tables retain their original structure without triggers, while a separate change tracking table records updates. This segmentation eliminates the need for complex triggers on base tables and simplifies the overall system architecture.
Solution Approach 2:
The patent introduces a change tracking table as an intermediary between base tables and materialized views. This intermediary table captures row changes without modifying base table structures, thereby simplifying the update mechanism while maintaining data accuracy in materialized views.
2Reliability
If the entire materialized view is re-processed to ensure updated data, then data accuracy is maintained, but the update time increases significantly
Solution Approach 1:
Instead of re-processing the entire materialized view, the patent applies partial action by updating only the specific rows that have changed. The change tracking table enables identification of modified rows, allowing the system to refresh only the necessary portions of the materialized view, thereby reducing update time while maintaining data accuracy.
3Loss of information
If joins are performed on base tables to retrieve data, then meaningful information can be extracted, but the process becomes complex when data in underlying tables are updated
Solution Approach 1:
The patent performs preliminary action by pre-tracking changes in base tables through the change tracking table before materialized view updates are needed. This advance tracking simplifies the data retrieval process by providing ready-to-use change information, eliminating the need for complex real-time analysis during updates.
Data Source
AI summary
System and methods for presenting updated data using persisting views is disclosed. An example method comprises: identifying a first dataset and a second dataset; tracking updates to rows in each of the first dataset and the second dataset using a first local marker and a second local marker, respectively; performing a join between the first dataset and the second dataset; creating a materialized view using the join; identifying updates to one or more rows of the first dataset and the second dataset; identifying rows in the materialized view corresponding to the one or more rows of the first dataset and the second dataset with the identified updates, and updating, by one or more processing devices, the identified rows in the materialized view based on the identified updates to the one or more rows of the first dataset and the second dataset.