Database Query Plan Optimization via Join Predicate Restructuring
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current database systems face performance issues due to the omission of equal-cost query plans, leading to suboptimal execution times in SQL queries, as the query optimizer fails to consider all possible query plans effectively.
Innovation Solution
A data query method and apparatus that restructures join predicates, determines basic information, and applies join operation rules to identify all equal-cost query plans, ensuring that all candidate query plans are considered for optimal performance by transforming join operations using operational-level and predicate-level rules.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If the query optimizer uses traditional methods to determine query plans, then the query optimization process is simple, but many equal-cost query plans are omitted leading to suboptimal execution times
Solution Approach 1:
The query optimization process is segmented into distinct phases: generating the original query plan, restructuring join predicates, determining basic information, applying join operation rules, and identifying equal-cost query plans. This segmentation allows comprehensive exploration of query plans while maintaining manageable complexity through systematic processing steps.
Solution Approach 2:
The system performs preliminary actions by generating an original query plan and restructuring join predicates before optimizing. This preliminary processing ensures that all necessary information is prepared in advance, enabling the identification of multiple equal-cost query plans without redundant computations during the optimization phase.
2Productivity
If the query optimizer considers all possible query plans, then query performance improves, but the computational complexity and processing time increase
Solution Approach 1:
The system changes parameters by introducing constraint conditions derived from join operation rules to filter and identify equal-cost query plans. This parameter-based approach allows efficient exploration of the query plan space by systematically varying join predicate structures while maintaining cost equivalence, reducing the need to evaluate all possible plans.
Solution Approach 2:
The system creates copies of the original query plan through restructuring join predicates and generating equal-cost variants. By working with these copied and transformed versions rather than re-evaluating all possibilities from scratch, the system efficiently identifies optimal plans without redundant computational overhead.
3Adaptability or versatility
If join predicates are restructured and join operation rules are applied, then more equal-cost query plans are identified, but the processing complexity increases
Solution Approach 1:
The query optimization process is made dynamic through iterative restructuring of join predicates and application of join operation rules. The system dynamically generates and evaluates multiple query plan variants by transforming join predicates according to operational-level and predicate-level rules, adapting the search space exploration based on discovered equal-cost plans.
Solution Approach 2:
The system employs nested processing by determining basic information about restructured join predicates and then applying join operation rules at multiple levels (operational-level rules and predicate-level rules). This nested approach systematically explores query plan variations by embedding rule applications within the predicate restructuring framework, enabling comprehensive plan identification through layered analysis.
Data Source
AI summary
A data query method and apparatus, and a database system, where the method includes receiving a data query request, generating an original query plan according to the data query request, obtaining a candidate query plan set according to the original query plan, restructuring a join predicate in the original query plan, determining basic information of a restructured join predicate in the original query plan, determining a constraint condition of the restructured join predicate in the original query plan, determining an equal-cost query plan for the original query plan according to the basic information of the restructured join predicate in the original query plan and the constraint condition of the restructured join predicate in the original query plan, and performing querying according to the equal-cost query plan for the original query plan. Hence, data query performance can be improved.