Automatic SQL Query Rewriting for Analytic View Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Legacy reporting tools fail to leverage analytic views (AVs) effectively, requiring manual reauthoring and regeneration of queries, which is costly and impractical, especially since users are familiar with querying base tables and not AVs, and existing tools do not generate SQL using AV-specific extensions.
Innovation Solution
The system automatically retrofits a hardware-accelerated view onto legacy queries, allowing them to utilize analytic views without modifying queries, by analyzing metadata and transforming queries to take advantage of AV optimizations such as join elimination and aggregation caching, using techniques like base table transforms and dense key generation.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Speed
If manual reauthoring and regeneration of queries is performed to leverage analytic views, then query performance is improved, but development cost and time are increased
Solution Approach 1:
The system performs automatic query transformation where the database management system itself rewrites legacy queries to use analytic views without requiring user intervention. The query transformation logic automatically identifies opportunities to use analytic views and rewrites the queries accordingly, making the system self-serving rather than requiring manual reauthoring by developers.
Solution Approach 2:
The system pre-compiles analytic view metadata and prepares transformation rules in advance so that when a legacy query is executed, the transformation can occur immediately without requiring manual reauthoring. The analytic view definitions and their associated metadata are prepared beforehand, enabling automatic query rewriting at query execution time.
2Speed
If legacy queries are modified to use analytic views, then query performance is improved, but ease of operation is reduced
Solution Approach 1:
The system introduces an automatic query transformation layer that acts as an intermediary between the user's legacy query and the analytic view infrastructure. Users continue to write queries in familiar SQL against base tables, and the transformation layer automatically rewrites them to use analytic views, thus mediating between user convenience and performance optimization without requiring users to learn new syntax or concepts.
3Speed
If existing query libraries are retargeted to analytic views, then performance is improved, but device complexity is increased
Solution Approach 1:
The database management system automatically applies query transformation to entire query libraries without requiring external tools or manual intervention. The system self-identifies legacy queries in the query library, analyzes them against available analytic views, and rewrites them automatically, making the complexity management self-service rather than requiring external complexity management tools.
Data Source
AI summary
Herein, a computer stores metadata that defines an analytic view that is based on organizing operation(s) that respectively specify organizing column(s) of organizing table(s), including a join operation that specifies a dimension column of a dimension table and a join column of a fact table. An original query is received that does not reference the analytic view. The original query specifies at least one organizing operation that is identical, somewhat similar, or compatible to an organizing operation on which the analytic view is based. Based on that at least one organizing operation that the original query specifies and the metadata that defines the particular analytic view, the original query is determined to satisfy rewrite criterion(s). In response to determining that the original query satisfies the rewrite criteria, a rewritten query that references the analytic view is generated based on the original query that does not reference the analytic view.