Flattened Table Live-Aggregate Projection Refresh
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Materialized views (MVs) in databases become outdated and are refreshed unnecessarily, leading to performance degradation due to unclear update rules and the need to update entire MVs when only specific columns or partitions have changed.
Innovation Solution
The introduction of a flattened table (FT) that logically materializes columns over partitions, allowing for on-demand refreshing on a per-column, per-partition basis, and live-aggregate projections (LAPs) that are automatically refreshed when underlying data changes, clarifying update rules and optimizing refresh processes.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If materialized views are refreshed automatically when underlying data changes, then data accuracy is maintained, but processing overhead increases and performance degrades
Solution Approach 1:
The patent segments the materialized view into multiple projections organized in a tree structure, where each projection represents a subset of columns. This allows selective refreshing of only the affected projections rather than the entire materialized view, reducing processing overhead while maintaining data accuracy for changed data.
Solution Approach 2:
The patent implements local quality by enabling different refresh behaviors for different projections within the same materialized view. Projections are selectively refreshed based on whether their underlying columns have changed, allowing the system to maintain high data accuracy where needed while minimizing unnecessary processing in unchanged areas.
2Reliability
If entire materialized views are refreshed when specific columns change, then data consistency is maintained, but unnecessary processing occurs
Solution Approach 1:
The patent divides the materialized view into segmented projections that can be independently identified and refreshed. When a column change occurs, the system segments the refresh operation to affect only the specific projections that depend on the changed column, rather than refreshing the entire materialized view.
Solution Approach 2:
The patent applies partial action by performing refresh operations on only the necessary subset of projections rather than the complete materialized view. This eliminates excessive processing while ensuring that all projections requiring updates are properly refreshed to maintain data consistency.
3Ease of operation
If clear update rules are implemented for materialized views, then database administrator control is improved, but system complexity increases
Solution Approach 1:
The patent implements self-service by enabling database administrators to define refresh rules and projection structures that automatically guide the refresh process. The system uses these administrator-defined rules to autonomously determine which projections need refreshing, reducing the complexity of manual control while improving ease of operation.
Data Source
AI summary
A flattened table (FT) of a database of a database management system (DBMS) is defined. The FT logically materializes a number of columns over a number of partitions. The columns include normalized columns, denormalized columns from a plurality of source tables of the database, as well as an aggregate column defining an aggregation of a selected normalized column over a selected denormalized column. A live-aggregate projection (LAP) is defined on the FT of the database. The LAP corresponds to the aggregate column and physically materializes the aggregation of the selected denormalized column over the selected denormalized column, as defined by the aggregate column. The FT is refreshed on-demand, on a per-column, per-partition basis. Responsive to the FT being refreshed on-demand, the LAP is automatically refreshed.