Materialized View Delta Computation for Database Maintenance
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current database systems face challenges in maintaining immediate materialized views, as recomputation is costly and incremental updates are often unavailable due to difficulties in determining what has been updated in the underlying base tables.
Innovation Solution
A method and system for immediately updating materialized views by computing a delta of the base table and materialized view, using delta computation and propagate-apply statements to apply changes directly, ensuring the materialized view reflects current data without full recomputation.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Loss of energy
If incremental update is used to maintain materialized view, then computational cost is reduced, but it is difficult to determine precisely what has been updated in the base table
Solution Approach 1:
The patent introduces a trigger as an intermediary mechanism that automatically activates when the base table is updated. This trigger captures the update information and passes it to the materialized view maintenance process, solving the problem of detecting what has been updated without requiring complex querying of the base table.
Solution Approach 2:
The patent performs preliminary action by pre-defining the maintenance logic in a trigger that is attached to the base table. When an update occurs, the trigger automatically executes the necessary maintenance operations on the materialized view, eliminating the need to determine what has been updated after the fact.
2Reliability
If full recomputation is used to maintain materialized view, then data accuracy is ensured, but computational cost increases significantly
Solution Approach 1:
The patent applies partial action by updating only the affected portions of the materialized view rather than performing a full recomputation. The trigger identifies specific updated rows and applies maintenance operations only to those rows, ensuring data accuracy while significantly reducing computational cost compared to full recomputation.
3Reliability
If immediate maintenance of materialized view is implemented, then data accuracy is maintained, but system complexity increases
Solution Approach 1:
The patent implements self-service by using a trigger that automatically maintains the materialized view when the base table is updated. The system serves itself by having the trigger handle the maintenance logic without requiring external intervention or complex scheduling mechanisms, thus maintaining data accuracy while managing system complexity.
Data Source
AI summary
A system, method, and computer program product are provided for immediately updating a materialized view in a relational database management system. The method includes the steps of creating a materialized view defined over a base table, executing an update statement on the base table, computing a delta of the base table by comparing images of the base table before execution of the update statement and after execution of the update statement, computing a delta of the materialized view based on the delta of the base table, and updating the materialized view using the delta of the materialized view.