Bushy Join Optimization for Database Query Efficiency
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Conventional database query processing methods face inefficiencies in executing queries with multiple large fact tables, as they require evaluating numerous permutations of bushy join trees, which is computationally costly and time-consuming, especially when the relative sizes of tables are not considered.
Innovation Solution
The method involves analyzing queries to identify satellite and seed tables, forming candidate bushy tree operation plans with sub-joins of seed and satellite tables, and selecting the least costly operation plan to reduce processing time and data transfer costs, independent of table sizes and query types.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If conventional methods evaluate all permutations of bushy join trees, then complete optimization is achieved, but computational cost and execution time increase significantly
Solution Approach 1:
The patent segments the evaluation process by dividing all possible bushy join tree permutations into multiple batches, where each batch contains a subset of permutations. This allows the system to evaluate permutations in manageable groups rather than all at once, reducing memory pressure and enabling early termination when optimal plans are found.
Solution Approach 2:
The patent performs preliminary actions by pre-computing join orderings using dynamic programming to determine optimal linear join sequences. These pre-computed orderings serve as templates that guide the subsequent evaluation of bushy join trees, reducing the search space and focusing computational effort on promising candidates.
2Reliability
If all bushy join tree combinations are evaluated, then optimal query plan is guaranteed, but system resources and processing overhead increase
Solution Approach 1:
The patent applies partial action by evaluating only a selected portion of all possible bushy join tree permutations rather than exhaustively evaluating every combination. The system uses heuristics and cost estimates to identify and evaluate the most promising permutations, achieving good optimization results without the full computational burden of complete enumeration.
Solution Approach 2:
The patent changes parameters by dynamically adjusting evaluation thresholds, batch sizes, and cost function weights based on query characteristics and system state. This allows the optimization process to adapt to different scenarios, balancing thoroughness with resource constraints by modifying evaluation parameters rather than following a fixed exhaustive approach.
3Productivity
If bushy joins are used for snowstorm queries, then query performance improves, but the method does not generalize to other query types
Solution Approach 1:
The patent creates a universal bushy join evaluation framework that can handle multiple query types beyond snowstorm queries. The system uses generic cost estimation functions and permutation generation mechanisms that work with various join scenarios, making the optimization approach applicable to different query patterns while maintaining the performance benefits for snowstorm queries.
Data Source
AI summary
A received query is analyzed to provide a set of potential bushy tree operation plans that include the creation of one or more “sub-joins” that provide intermediate resultant “sub-tables”, and using these sub-tables as operands in the other predicates of the query. The received query is analyzed to identify “satellite” tables and “seed” tables. A satellite table is a table of the database that is filtered by a selective predicate, and is connected to only one other table in the database via at least one linking predicate, and a seed table is a table of the database that is connected to a satellite table and at least one other table. Multiple candidate operation plans are formulated as bushy trees that include a sub-join of each seed table and its connected satellite table. A least cost operation plan is selected for execution from among these candidate operation plans and the conventional least cost linear operation plan based on the costs associated with each operation plan.