Join Dependent Refresh for Materialized Views
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current methods for performing Partitioned Change Tracking (PCT) fast refresh operations in Materialized Views (MVs) are computationally expensive due to the need for joining change logs with MVs, which can be time-consuming and costly, especially when dealing with large logs and complex queries.
Innovation Solution
The implementation of a join dependent refresh method that uses join dependencies to derive and fill identifiers of rows or partitions in refresh expressions, allowing for efficient identification and updating of affected rows or partitions without the need for extensive joining, thereby reducing computational costs.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If conventional PCT fast refresh methods are used to update materialized views, then the MVs can be refreshed with change tracking, but the computational cost is high due to joining change logs with MVs
Solution Approach 1:
The patent extracts and eliminates the expensive join operation between change logs and materialized views. Instead of performing full joins to identify affected rows, the invention directly uses change log entries to update corresponding MV rows, removing the unnecessary joining step while maintaining refresh accuracy.
Solution Approach 2:
The patent performs preliminary actions by pre-establishing the relationship between base tables and materialized views during the MV creation phase. This allows the refresh process to directly map change log entries to MV rows without needing to perform complex joins at refresh time, thereby reducing computational cost.
2Measurement precision
If change logs are joined with materialized views to identify affected rows, then accurate refresh can be performed, but the operation becomes time-consuming with large logs
Solution Approach 1:
The patent removes the time-consuming join operation from the refresh process. By directly mapping change log entries to materialized view rows using pre-established relationships, the system achieves accurate row identification without the computational overhead of joining large change logs with MVs.
Solution Approach 2:
The invention performs preliminary setup during MV creation to establish direct mapping relationships between base tables and MVs. This preliminary action enables the refresh process to quickly identify affected rows without performing time-consuming joins, even when dealing with large change logs.
3Adaptability or versatility
If complex queries are used to define materialized views, then comprehensive data aggregation is achieved, but the join operations become more costly during refresh
Solution Approach 1:
The patent extracts and eliminates the complex join operations from the refresh process. By using direct mapping relationships established during MV creation, the system can handle complex MV definitions without incurring proportional increases in refresh complexity, as the join operations are removed entirely.
Solution Approach 2:
The invention performs preliminary analysis and relationship establishment during the complex MV query definition phase. This allows the system to capture comprehensive data aggregation requirements while simplifying the refresh operation, as all complex joining logic is resolved in advance rather than during refresh execution.
Data Source
AI summary
In an embodiment, Partition Change Tracking (PCT) refresh is performed by using join dependencies on partitioning columns to derive and fill in identifiers of rows and/or partitions containing the rows in the refresh expressions. In an embodiment, the join dependencies that form the Materialized View's (MV's) query definition and the partitioning predicate of the table are used to automatically generate a statement which is run in order to derive and fill in identifiers of the rows and/or partitions that are being changed. The identifiers are then plugged into the refresh expression, and the refresh expression is run. Running the refresh expression causes the rows identified to be refreshed.