Selective Materialized View Refresh via Partition Segmentation
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current approaches to refreshing materialized views in database management systems require significant computer resources and involve refreshing the entire view, which can be inefficient and time-consuming, especially when only a portion of the data has changed.
Innovation Solution
Implementing a selective refresh method that allows only the affected portions of a materialized view to be updated, using either user-input or change data to determine which parts of the view need to be refreshed, and employing different refresh techniques such as complete refresh, PCT refresh, or log-based refresh for specific partitions or subsets of data.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If the entire materialized view is refreshed, then the data consistency with base tables is improved, but the computational resources and time required increase significantly
Solution Approach 1:
The materialized view is divided into multiple partitions, and the refresh operation is applied selectively to only those partitions that contain changed data. This segmentation allows the system to maintain data consistency for affected portions without incurring the cost of refreshing the entire materialized view, thus resolving the contradiction between reliability and computational resource consumption.
Solution Approach 2:
Instead of performing a complete refresh of the materialized view, the system performs a partial refresh only on the specific partitions that have been affected by changes in the base tables. This partial action is sufficient to maintain data consistency where needed while avoiding unnecessary computational overhead on unchanged data.
2Reliability
If the entire materialized view is refreshed, then the data freshness is improved, but the time required for refresh operations increases
Solution Approach 1:
The materialized view is segmented into multiple partitions, enabling the system to refresh only the specific partitions that contain changed data. This segmentation reduces the overall refresh time significantly, as the system avoids reprocessing unchanged partitions while still maintaining data freshness for affected portions.
Solution Approach 2:
The system performs a partial refresh on only those partitions that have been modified in the base tables. This partial action is sufficient to maintain data freshness for the affected data while dramatically reducing the time required compared to a complete refresh of the entire materialized view.
3Productivity
If selective refresh is implemented, then the computational efficiency is improved, but the system complexity increases
Solution Approach 1:
The materialized view is divided into manageable partitions, and the system tracks changes at the partition level. This segmentation enables selective refresh operations that improve computational efficiency. The complexity introduced by partitioning is offset by the ability to perform targeted updates rather than full refreshes, making the system more efficient overall.
Solution Approach 2:
The system implements a feedback mechanism that tracks changes in base tables and identifies which materialized view partitions are affected. This feedback information is used to guide the selective refresh process, ensuring that only necessary partitions are updated. The feedback mechanism manages the added complexity by providing automated decision-making logic for refresh operations.
4Loss of time
If selective refresh is implemented, then the refresh time is reduced, but the complexity of determining which portions to refresh increases
Solution Approach 1:
The materialized view is partitioned into discrete units, and the system tracks which partitions contain changed data. This segmentation simplifies the determination process by breaking down the complex decision of what to refresh into manageable partition-level decisions. The system can quickly identify affected partitions without analyzing the entire materialized view.
Solution Approach 2:
The system uses a feedback mechanism that automatically tracks changes in base tables and propagates this information to identify affected materialized view partitions. This feedback-driven approach reduces the complexity of determining which portions to refresh, as the system automatically identifies affected partitions based on change tracking rather than requiring complex analysis of the entire view.
Data Source
AI summary
Techniques are provided for selectively refreshing a materialized view. A portion of a materialized view is refreshed (or updated) while other portions of the materialized view are not refreshed. Thus, the other portions of the materialized view may contain “stale” data that does not accurately reflect current data in the base table(s) upon which the materialized view is based. In one technique, a user indicates the portion of the materialized view that is to be refreshed. In another technique, a user indicates a plurality of portions of a materialized view and it is automatically determined which portion should be refreshed first. Change data that indicates information about changes made to different portions of one or more base tables and/or read data that indicates information about reads to different portions of the materialized view may be taken into account to determine which portion of a materialized view to refresh.