OR-Expansion Query Optimization via DNF Conversion
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Query optimization techniques face challenges in efficiently transforming queries with complex logical combinations of predicates due to time and resource constraints, often resulting in sub-optimal transformations, especially for queries with disjunctive predicates.
Innovation Solution
The implementation of an OR-expansion technique that converts disjunctive predicates into disjunctive normal form (DNF) and uses the UNION ALL operator to break up predicates into independent conjuncts, allowing for more efficient index usage and improved execution plans.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If query optimization techniques are applied to transform queries with complex logical combinations of predicates, then query execution efficiency is improved, but the transformation process consumes excessive time and computational resources
Solution Approach 1:
The patent segments the complex query transformation problem into distinct phases: initial DNF conversion, iterative OR-expansion state exploration, and cost-based selection. Each phase handles a specific aspect of the transformation, allowing the system to manage complexity systematically while balancing optimization quality with resource consumption.
Solution Approach 2:
The patent implements dynamic control of the OR-expansion process by maintaining a set of candidate queries with different expansion states and iteratively selecting which candidates to explore based on cost estimates. This dynamic approach allows the system to adapt the level of optimization achieved based on available resources and query characteristics.
2Productivity
If exhaustive OR-expansion states are explored to find the most optimal execution plan, then query performance is maximized, but computational complexity and resource consumption increase
Solution Approach 1:
The patent applies partial action by exploring only a subset of all possible OR-expansion states rather than exhaustively evaluating every possibility. The system generates candidate queries with different expansion states and selectively processes them based on cost estimates, achieving good optimization results without the prohibitive complexity of complete enumeration.
Solution Approach 2:
The patent changes the parameter of expansion depth by controlling how many OR-expansion states are explored. Instead of fixed exhaustive exploration, the system dynamically adjusts the number of candidate states to evaluate, balancing optimization quality with computational resource consumption based on query characteristics and system conditions.
3Productivity
If disjunctive predicates are converted to disjunctive normal form using UNION ALL operator, then index usage efficiency is improved, but the query structure becomes more complex
Solution Approach 1:
The patent segments the query into multiple candidate representations with different UNION ALL expansion states. Each candidate query represents a different way of structuring the disjunctive predicates, allowing the system to select the structure that best balances index usage efficiency with overall query complexity based on cost estimation.
Data Source
AI summary
Methods, stored instructions, and computer systems are described for selecting an OR-expansion state of a query. A query processor accesses a certain query and a control parameter. The certain query comprises disjunctive predicates, and the control parameter indicates a certain procedure of a plurality of alternative stored procedures for expanding queries comprising disjunctive predicates into semantically equivalent candidate queries comprising combinations of subqueries. Based at least in part on the control parameter, the query processor selects the certain procedure to expand the certain query into one or more certain semantically equivalent candidate queries, each comprising a different combination of two or more subqueries. The query processor causes cost-based evaluation of subject queries, including the certain query and the one or more certain semantically equivalent candidate queries. The query processor then selects a query of the subject queries for execution or for further evaluation or transformation.