Canonical Range Representation for Materialized View Query Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Large databases with base tables often result in lengthy query times due to the vast volumes of data, necessitating a more efficient method to retrieve data without excessive computational resources.
Innovation Solution
Implementing a materialized view query rewrite (MVQR) component that translates range predicates from queries and materialized views into Canonical Range Representation (CRR) format, enabling transparent translation and efficient data retrieval by matching query metadata with materialized view metadata to utilize the materialized views instead of base tables.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Loss of time
If queries search base tables directly, then data completeness is ensured, but query time increases significantly
Solution Approach 1:
The patent introduces a materialized view as an intermediary structure between the base table and the query. The materialized view contains pre-computed results and serves as a mediator that can satisfy certain queries without accessing the full base table, thereby reducing query time while maintaining data accuracy for supported queries
Solution Approach 2:
The system performs preliminary computation by pre-calculating and storing results in materialized views before queries are executed. This preliminary action allows the query optimizer to retrieve pre-computed results instead of performing computations at query time, significantly reducing query execution time
2Productivity
If materialized views are used, then query performance improves, but translation complexity increases
Solution Approach 1:
The patent transforms range predicates from their original query form into a canonical range representation format. This parameter transformation standardizes the representation of range conditions, enabling systematic comparison between query metadata and materialized view metadata, and facilitating automated determination of whether a materialized view can satisfy a given query
Solution Approach 2:
The system replaces manual or complex ad-hoc analysis of query-materialized view compatibility with an automated metadata comparison mechanism. By encoding range predicates in canonical form and implementing systematic comparison algorithms, the system substitutes complex mechanical analysis with automated computational processes
3Measurement precision
If canonical range representation is implemented, then query matching accuracy improves, but computational overhead increases
Solution Approach 1:
The system performs the computationally intensive task of translating range predicates into canonical range representation during the metadata preparation phase, before actual query execution. This preliminary translation enables efficient comparison operations during query processing, as the canonical form allows for straightforward, low-cost matching operations
Data Source
AI summary
A system and methods for implementing a materialized view for a query are provided. The query system includes a database that includes a base table. The base table can include a set of data. The query system also includes a materialized view associated with a portion of the set of data, and a MVQR component to translate each of a range predicate of the materialized view and a range predicate of a query into a canonical range representation (CRR) format in materialized view metadata and query metadata, respectively, to enable the query to search the materialized view.