Query Re-write Framework for Database Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Complex database queries involving multiple tables often require re-writing to optimize processing efficiency, but ordinary users lack expertise in modeling or database structure to effectively reorder table merges, leading to inefficient query execution.
Innovation Solution
A query re-write framework with a query normalization engine and a rule catalog of re-write rules allows users to visualize and optimize query plans by applying rules in a specific sequence, enabling re-ordering of table merges for improved efficiency.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If complex queries are executed using default table merge sequences, then query execution can proceed without user intervention, but processing efficiency deteriorates due to suboptimal merge ordering
Solution Approach 1:
The system automatically rewrites queries by applying optimization rules from the rule catalog without requiring user expertise in database modeling or query optimization. The query normalization engine self-services by detecting suboptimal merge sequences and rewriting them using the stored rules, eliminating the need for users to manually optimize complex queries.
Solution Approach 2:
The system performs preliminary actions by pre-storing query rewrite rules in the rule catalog that encode optimal merge sequences. These rules are prepared in advance based on database schema analysis and performance characteristics, so when a query needs optimization, the engine can immediately apply pre-computed rewrite rules rather than performing complex optimization analysis at query execution time.
2Productivity
If query rewrite rules are applied automatically without user control, then query optimization can proceed without user intervention, but adaptability deteriorates as users cannot customize optimization behavior
Solution Approach 1:
The system implements dynamic control by allowing users to enable or disable specific rewrite rules from the rule catalog based on their needs. The query normalization engine can adapt its behavior by selecting different subsets of rules from the catalog, providing both automatic optimization and user customization. Users can dynamically adjust which optimization rules are applied to different queries or query types.
Data Source
AI summary
Embodiments relate to re-writing database query plans, and visualizing such re-written query plans. A query re-write framework includes a query normalization engine in communication with a rule catalog comprising query re-write rules in the form of rule classes. The framework receives as input, a query plan graph to be re-written. Based upon the engine's application of re-write rules from the catalog, the framework produces a re-written query plan graph as output. An interface component of the framework may provide a visualization of the re-written query plan graph as part of a dashboard. A user may access the framework to enable/disable existing rules in the catalog, add new rules to the catalog, and/or control a sequence and a precedence in which rules are applied to re-write the query plan. A user may interact with the visualization of the re-written query plan for purposes of de-bugging, re-write optimization, and/or query development.