User Defined Heuristic Refresh for Materialized Query Tables
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database systems face inefficiencies in updating materialized query tables (MQTs), as fixed update frequencies can result in stale data or wasteful resource usage, particularly when user-defined data is involved, as current algorithms do not effectively account for the specific impacts of changes to base tables on MQT freshness.
Innovation Solution
Implementing user-defined heuristic freshness conditions (HFCs) to determine when to update MQTs, using low-cost queries that require minimal computing resources, allowing updates only when necessary based on specific user-defined conditions, and extending query language syntax to incorporate HFCs within SQL commands.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If MQTs are updated frequently to maintain data freshness, then data accuracy is improved, but resource consumption increases
Solution Approach 1:
The patent implements dynamic update frequency adjustment based on user-defined heuristic freshness conditions. Instead of fixed periodic updates, the system adapts update timing to actual data change patterns and user-defined freshness requirements, optimizing the balance between data freshness and resource consumption
Solution Approach 2:
The system changes the parameter of update frequency from a fixed value to a dynamic value determined by heuristic freshness conditions. These conditions evaluate data age, change frequency, and user-defined requirements to determine optimal update timing, thereby reducing unnecessary updates while maintaining data freshness
2Productivity
If MQTs are updated only when necessary to reduce resource waste, then resource efficiency is improved, but data freshness may deteriorate
Solution Approach 1:
The system implements feedback mechanisms through heuristic freshness conditions that continuously monitor data age, change patterns, and user-defined requirements. This feedback loop determines when updates are truly necessary, ensuring data freshness is maintained only when actually needed rather than on fixed schedules
Solution Approach 2:
The system performs preliminary evaluation of freshness conditions before executing updates. By pre-assessing whether data actually needs updating based on user-defined criteria, the system avoids unnecessary update operations while ensuring freshness requirements are met when critical
3Device complexity
If fixed update frequency is used for MQTs, then system simplicity is maintained, but adaptability to user-defined data changes is reduced
Solution Approach 1:
The patent transforms the static update frequency into a dynamic parameter controlled by user-defined heuristic freshness conditions. This allows the update mechanism to adapt to different data change patterns and user requirements without requiring complex manual configuration or management intervention
Data Source
AI summary
A method, system and computer program product for operating a database includes: receiving a query including a first component indicating a criteria for generating a materialized query table (MQT) from the database and a second component having a condition for generating a reference value indicating a state of one or more items in the database; generating the MQT from the database where the MQT includes data items satisfying the criteria; generating, by executing the second component in response to receiving the query, a first reference value; receiving, subsequent to generating the first reference value, a second query for retrieving a data item from the MQT; generating, by executing the second component in response to receiving the second query, a second reference value; determining whether the first and second reference values are equal; and updating the MQT when the first reference value is not equal to the second reference value.