Incremental Maintenance of Outer-Join Materialized Views
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current database systems lack efficient algorithms for maintaining materialized views with outer-joins and aggregated views, particularly when dealing with multiple outer joins, leading to increased maintenance overhead and inefficiency.
Innovation Solution
The development of algorithms that convert outer-join expressions into join-disjunctive normal form and utilize database constraints, such as foreign-key constraints, to reduce maintenance overhead, enabling efficient incremental maintenance of materialized views with any combination of selection, projection, inner joins, one-sided outer joins, and full outer joins, including aggregated views.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If conventional mechanisms for maintaining outer-join views are used, then outer-join views can be maintained, but maintenance overhead becomes grossly inefficient for views with more than a single outer join
Solution Approach 1:
The patent segments the maintenance problem by dividing outer-join view maintenance into distinct cases based on join type (left outer join, right outer join, full outer join) and aggregation presence. Each segment has its own optimized maintenance algorithm, avoiding the inefficiency of generic approaches. The view maintenance is broken down into identifying affected tuples, computing differences, and applying updates in a structured sequence.
Solution Approach 2:
The patent performs preliminary actions by pre-identifying affected tuples before executing maintenance operations. It uses foreign key constraints to pre-determine which base table tuples will be affected by updates, allowing the system to prepare maintenance expressions in advance. This preliminary identification of affected tuples significantly reduces the computational overhead during actual view maintenance.
2Speed
If incremental maintenance algorithms are applied to SPOJG views, then query processing speed is improved, but maintenance complexity increases due to handling multiple join types and aggregations
Solution Approach 1:
The patent applies local quality by tailoring maintenance algorithms to specific view characteristics. Different maintenance strategies are applied based on the local properties of each view: whether it contains aggregation, which side the outer join is on, and which base tables are involved. This localized approach optimizes each maintenance operation for its specific context rather than using a one-size-fits-all algorithm.
Solution Approach 2:
The patent changes parameters by dynamically adjusting maintenance expressions based on foreign key constraint directions and join types. The maintenance algorithm modifies its behavior parameters (which tuples to affect, how to compute differences) according to the specific view definition and underlying schema constraints, enabling efficient handling of diverse SPOJG view configurations.
Data Source
AI summary
Maintenance of materialized view for query optimization in a database. The architecture provides the first practical algorithms for incrementally maintaining views with multiple outer joins and aggregated outer-join views. Materialized views with any combination of inner joins, one-sided outer joins and full outer joins, plus an optional aggregation on top, can be processed by algorithm(s) that construct incremental maintenance expressions based on converting the view definition a join-disjunctive normal form and exploiting database constraints to reduce maintenance overhead. A system comprises a view creation component for creating a materialized view definition that includes outer-joins, a conversion component for converting an outer-join view expression into a normal form, and a maintenance component for updating the stored materialized view result associated with the materialized view definition.