Rule-Based Join Order Hints for Stable Query Plans
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database systems face challenges in maintaining responsive query plan stability and efficiency, particularly in cloud-based data warehouses, due to the complexity and scale of data management, leading to increased query response times and resource consumption.
Innovation Solution
Implementing a rule-based hint application framework within the query optimizer to apply join order hints through transformation-based rules, such as join permute and commute rules, to optimize query plans and reduce the computational overhead of Cost-Based Optimization.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If Cost-Based Optimization is used to determine join orders, then query optimization accuracy is improved, but computational overhead and query response time increase
Solution Approach 1:
The system pre-generates and stores multiple candidate query plans with different join orders in a plan cache before query execution. When a query arrives, the system retrieves pre-computed plans from cache rather than performing expensive CBO calculations at query time, thus reducing query response time while maintaining optimization accuracy through pre-computed optimal plans.
Solution Approach 2:
The query optimization process is divided into two separate phases: an offline phase where multiple candidate query plans are generated and stored in cache, and an online phase where the system selects from pre-computed plans. This segmentation allows computationally intensive CBO operations to be performed offline, reducing online query response time while preserving optimization accuracy.
2Stability of the object's composition
If multiple candidate query plans are generated and cached, then query plan stability is improved, but memory resource consumption increases
Solution Approach 1:
The system dynamically adjusts the number of candidate query plans cached based on query characteristics, data warehouse size, and available memory resources. By changing the parameter of cache size and number of candidate plans, the system balances query plan stability with memory resource consumption, caching more plans for stable queries while using fewer plans for resource-constrained scenarios.
Data Source
AI summary
The subject technology receives a first query plan corresponding to a query, the first query plan comprising a set of join nodes, and a join order hint of a target query plan, each join node of the target query plan associated with a hint. The subject technology generates a second query plan to correct a set of vertical positions of a set of terminal nodes of the first query plan following the join order hint of the target query plan. The subject technology generates a third query plan to correct a set of lateral positions of the set of terminal nodes of the second query plan following the join order hint of the target query plan. The subject technology, for each join node from the set of join nodes of the third query plan, indicates that each join node has been hinted. The subject technology generates, after each join node of the third query plan has been indicated as being hinted, the target query plan based at least in part on the third query plan.