Automatic Materialized View Generation via Extended Covering Subexpression
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Manually identifying optimal materialized views in database management systems is time-consuming and requires extensive knowledge, as it involves selecting reasonable-sized views that can rewrite a substantial number of workload queries and perform pre-computation of joins and grouping.
Innovation Solution
The automatic generation of materialized views using the extended covering subexpression technique (ECSE), which examines query blocks to identify candidate materialized views by applying set operations such as equivalence, intersection, subset, superset, and union to generate new resultant join sets, thereby reducing the need for manual intervention.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Manufacturing precision
If materialized views are manually identified, then the quality and optimality of materialized views can be ensured, but the time and effort required increases significantly
Solution Approach 1:
The system performs self-service by automatically generating materialized views through the ECSE algorithm. The database management system examines query blocks, identifies candidate materialized views, and generates them without human intervention, allowing the system to serve itself in the view generation process while maintaining quality through systematic evaluation criteria
Solution Approach 2:
The manual mechanical process of identifying materialized views is replaced with an automated algorithmic system. The ECSE (Extended Covering Subexpression) technique substitutes human expert analysis with a computational approach that systematically processes query blocks, applies set operations, and generates candidate views based on predefined criteria
2Productivity
If extensive manual analysis is performed to identify optimal materialized views, then the number of queries that can be rewritten increases, but the complexity of the process increases
Solution Approach 1:
The complex identification process is segmented into distinct manageable steps: examining query blocks, identifying candidate materialized views through set operations (equivalence, intersection, subset, superset, union), evaluating candidates against criteria, and generating final views. This segmentation breaks down the complexity while systematically increasing the number of rewriteable queries
Solution Approach 2:
The system applies partial action by focusing on generating a sufficient number of candidate materialized views rather than exhaustively analyzing all possible views. The ECSE algorithm identifies candidates that meet minimum thresholds for usefulness, achieving high productivity without requiring complete exhaustive analysis
3Loss of time
If automated techniques are used to generate materialized views, then the time and effort required is reduced, but the precision and quality may decrease
Solution Approach 1:
The automated system incorporates feedback mechanisms by evaluating candidate materialized views against established criteria before final generation. The ECSE algorithm assesses candidates based on their ability to rewrite queries, their size, and their computational benefits, providing feedback that ensures only quality views are generated while maintaining automated efficiency
Solution Approach 2:
The system maintains precision through parameter-based control of the generation process. By adjusting parameters such as the thresholds for candidate selection, the types of set operations applied, and the evaluation criteria weights, the system can optimize both speed and quality of generated materialized views according to specific requirements
Data Source
Figure 1
Figure 2
Figure 3
AI summary
Definitions of material views are automatically generated. In general, Automated MV generation identifies a set of candidates MVs by examining a working set of query blocks. Once the candidates are formed, the candidate MVs are further evaluated to calculate a benefit to the candidate MVs. An improved approach for generating a candidate set of MVs is described herein. The improved approach is referred to as the extended covering subexpression technique (ECSE). Under ECSE, various relationships between join sets other than strict equivalence are used to generate new resultant join sets. Such relationships include subset, intersection, superset, and union, which shall be described in further detail below. In some cases, relationships among resultant join sets and initial join sets are considered to generate new resultant join sets. The final resultant join sets are then used to form a candidate set of MVs.