Dynamic Materialized View Placement Across Database Engines
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing systems for optimizing database physical designs focus on single queries, leading to sub-optimal performance when considering multiple queries, as they do not account for the potential benefits of moving materialized views between execution engines to reduce overall workload cost.
Innovation Solution
The system leverages opportunistic materialized views to dynamically move views across multiple database engines during query processing, allowing stages of a query to execute on multiple engines and optimizing the physical design configuration to minimize the total execution cost of a workload by considering the unique capabilities of each engine.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Speed
If materialized views are moved between execution engines to optimize workload performance, then query processing speed improves, but storage space requirements increase
Solution Approach 1:
The system dynamically moves materialized views between execution engines based on workload characteristics and engine capabilities. Views are not fixed to a single engine but can be opportunistically transferred to where they provide maximum benefit, making the storage configuration adaptive rather than static.
Solution Approach 2:
The system changes the physical location parameter of materialized views by moving them between execution engines. This parameter change is driven by analyzing workload patterns and engine performance characteristics to optimize query processing speed while managing storage space consumption.
2Productivity
If multiple materialized views are moved across engines to reduce total execution cost, then workload performance improves, but device complexity increases
Solution Approach 1:
The system automatically analyzes workload patterns and engine capabilities to determine optimal view movement decisions without requiring manual intervention. The self-service mechanism includes automatic cost estimation, benefit calculation, and execution of view transfers based on predefined criteria and heuristics.
Solution Approach 2:
The system continuously monitors workload execution and engine performance to gather feedback on view movement effectiveness. This feedback is used to refine future view placement decisions, creating an iterative optimization process that adapts to changing workload characteristics and engine performance.
3Productivity
If a globally optimal solution for view movement is computed, then workload optimization improves, but computation time increases exponentially
Solution Approach 1:
Instead of computing the globally optimal view movement solution which requires exhaustive search of all possible configurations, the system applies partial optimization by considering only the most promising view movements based on heuristics and workload patterns. This partial action approach achieves sufficient optimization without the exponential time cost of global optimization.
Solution Approach 2:
The system performs preliminary analysis of workload patterns and engine capabilities to pre-determine which views are candidates for movement and under what conditions. This preliminary action reduces the search space for actual view movement decisions, avoiding the need to evaluate all possible configurations during execution.
Data Source
AI summary
Systems and methods are disclosed for managing a multi-store execution environment by applying opportunistic materialized views to improve workload performance and executing a plan on multiple database engines to increase query processing speed by leveraging unique capabilities of each engine by enabling stages of a query to execute on multiple engines, and by moving materialized views across engines.