Query Rewrite with Remote Materialized Views
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
In distributed database systems, queries cannot be efficiently rewritten to reference materialized views that contain remote database objects, limiting the use of query rewrite and database performance.
Innovation Solution
The solution involves rewriting queries to dynamically track the staleness state of materialized views and utilize inline views to transform queries to reference either local or remote materialized views, optimizing network communication and performance by determining the freshness of data and calculating network costs for join back operations.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If queries are rewritten to reference materialized views containing remote database objects, then database performance is improved, but the complexity of query processing increases
Solution Approach 1:
The patent introduces an intermediary mechanism that tracks staleness state of materialized views and determines whether to rewrite queries. This mediator component simplifies the complexity by providing a clear decision framework: if the materialized view is fresh, rewrite the query; if stale, do not rewrite. This intermediary layer manages the complexity of distributed query processing while maintaining performance benefits.
2Loss of energy
If query rewrite is limited to local materialized views only, then query processing remains simple, but network communication costs increase
Solution Approach 1:
The patent changes the parameter of materialized view accessibility from local-only to both local and remote. By allowing queries to reference materialized views stored in remote databases, the system reduces network communication costs by utilizing pre-computed results across the distributed system. This parameter change enables query rewrite to extend beyond local boundaries while managing the increased complexity through systematic staleness tracking.
3Adaptability or versatility
If materialized views are used to access remote database objects, then data accessibility is improved, but data freshness cannot be guaranteed
Solution Approach 1:
The patent implements a feedback mechanism through staleness state tracking of materialized views. The system continuously monitors whether materialized views are fresh or stale and uses this feedback information to determine whether to rewrite queries. This feedback loop ensures that only fresh materialized views are used for query rewriting, guaranteeing data freshness while maintaining the versatility of accessing remote database objects through materialized views.
Data Source
AI summary
A query statement, issued to a local database server, is re-written. The query references at least one of a first or a second object. The first object is remote with respect to the local database server, for accessing a first materialized view that is local or remote with respect to the local database server. The second object is local with respect to the local database server, to access a second materialized view that is remote with respect to the local database server. Rewriting the query can include dynamically tracking a staleness state associated with one or more of the materialized views.