Dynamic Materialized Query View Management for Database Workloads
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current database management systems face inefficiencies in managing materialized query tables (MQTs) due to pre-materialization approaches that fail to exploit all potential benefits, especially under disk space and maintenance cost constraints, leading to suboptimal query performance.
Innovation Solution
An automated, dynamic view management scheme that materializes MQTs on-demand during workload execution and manages them using a least recently used (LRU) cache, employing a genetic method to find the optimal query permutation that maximizes net benefits by balancing view materializations, base table accesses, and cache hits.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If materialized query tables are pre-materialized for the whole batch query workload, then the accumulated benefits of MQTs exceed the cost of their materialization, but the disk space requirements and maintenance costs increase significantly
Solution Approach 1:
The patent implements dynamic MQT management where the system automatically creates, maintains, and drops materialized query tables based on real-time workload analysis. Instead of pre-materializing all possible MQTs, the system dynamically selects which MQTs to materialize based on query patterns, resource availability, and benefit-cost analysis, allowing the MQT portfolio to adapt continuously to changing workload conditions
Solution Approach 2:
The system changes the state of MQT materialization from static (pre-defined) to dynamic (on-demand). It monitors query workload parameters, resource utilization metrics, and performance benefits to dynamically adjust which MQTs remain materialized. This parameter-based control allows the system to optimize the balance between query performance and disk space usage by materializing MQTs only when their benefit exceeds their cost
2Productivity
If materialized query tables are created for the whole batch query workload, then query processing cost is reduced significantly, but the cost of creating and maintaining MQTs increases
Solution Approach 1:
The patent implements a self-managing system that automatically monitors query workloads, analyzes the potential benefits of MQT materialization, and makes decisions about which MQTs to create and maintain. The system continuously evaluates whether the performance benefit of an MQT justifies its creation and maintenance costs, automatically dropping MQTs that no longer provide sufficient benefit. This self-service approach eliminates the need for manual MQT management while optimizing the balance between query processing efficiency and resource consumption
3Reliability
If MQTs are accessed from base tables, then data accuracy is maintained, but processing time increases compared to cache access
Solution Approach 1:
The system performs preliminary actions by materializing query results into MQTs before they are needed for actual query processing. When queries are executed, the system checks if pre-computed results exist in the MQT cache, allowing it to return results immediately without re-executing expensive base table operations. This preliminary materialization of frequently accessed data significantly reduces query processing time while maintaining data accuracy through periodic refreshes
Solution Approach 2:
The patent introduces MQTs as an intermediary layer between base tables and query processing. Instead of directly accessing base tables for every query, the system uses MQTs as intermediate storage that holds pre-computed results. This intermediary structure allows the system to serve queries from the faster MQT cache when data is available, reducing processing time while maintaining data accuracy through controlled synchronization with base tables
Data Source
AI summary
The embodiments of the invention provide a method, program storage device, etc. for automated and dynamic management of query views for database workloads. More specifically, a method begins by executing queries, which includes accessing a set of data tables for each of the queries. During the executing of the queries, the method accesses a required data table from a cache if the required data table is present in the cache and creates the required data table if the required data table is not present in the cache. The accessing of the required data table from the cache has a lower processing cost than accessing the required data table from a base table. Also during the executing of the queries, created data tables are stored in the cache, wherein one or more of the created data tables are removed from the cache when the cache becomes full.