Bushy Nested Loop Join Tree Generation via Left Deep Transformation
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current database optimizers face challenges in generating bushy trees for nested loop joins, particularly when using a bottom-up search strategy, leading to exponential complexity and increased memory usage, which hinders efficient query optimization and execution.
Innovation Solution
The development of a methodology that allows for the generation of bushy nested loop join trees within a nested loop join (NLJ) optimizer, utilizing a left deep operator tree search space and transforming it into a semantically correct bushy tree structure without increasing memory usage or requiring the building of a stored index.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Adaptability or versatility
If a bottom-up search strategy is used to generate bushy trees for nested loop joins, then the query optimization can capture complex query semantics, but the complexity increases exponentially and memory usage increases
Solution Approach 1:
The patent segments the bushy tree generation process into two distinct phases: (1) generating left-deep operator trees using a bottom-up search strategy, and (2) transforming selected left-deep trees into bushy trees. This segmentation allows the optimizer to benefit from the systematic approach of bottom-up search while avoiding its exponential complexity for bushy tree generation. The transformation phase converts the simpler left-deep structure into the desired bushy structure, capturing complex query semantics without the combinatorial explosion of directly generating bushy trees.
Solution Approach 2:
The patent performs preliminary generation of left-deep operator trees before transforming them into bushy trees. By first creating left-deep trees (which have a single linear chain of nested loop joins) and then transforming selected ones into bushy trees, the optimizer avoids the complexity of directly generating bushy trees. This preliminary action reduces the search space and memory requirements while still enabling the capture of complex query semantics through the subsequent transformation.
2Productivity
If bushy trees are generated for nested loop joins, then query execution efficiency can be improved, but memory usage increases
Solution Approach 1:
The patent segments the tree generation process to first create left-deep trees (which require minimal memory) and then transform selected ones into bushy trees. This segmentation allows the system to maintain low memory usage during the generation phase while still producing bushy trees for efficient query execution. The transformation is performed selectively on promising candidates rather than generating all possible bushy trees.
Solution Approach 2:
The patent uses left-deep operator trees as intermediate copies or representations that can be transformed into bushy trees. Instead of directly generating and storing complex bushy trees, the system first creates simpler left-deep tree copies and then transforms them as needed. This copying approach reduces memory requirements by working with simpler intermediate structures.
3Device complexity
If a left deep operator tree search space is used, then the search complexity is reduced, but the ability to generate optimal bushy tree structures is limited
Solution Approach 1:
The patent performs preliminary generation of left-deep operator trees using a simplified search space, then transforms selected trees into bushy structures. This preliminary action with left-deep trees reduces search complexity while the subsequent transformation step restores the ability to generate optimal bushy tree structures. The transformation phase converts the limited left-deep representation into the more versatile bushy form when beneficial.
Solution Approach 2:
Instead of directly generating bushy trees from the search space (which is complex), the patent inverts the approach by first generating left-deep trees (simple search space) and then transforming them into bushy trees. This inversion allows the system to benefit from the simplicity of left-deep tree generation while still achieving the versatility of bushy tree structures through the transformation process.
Data Source
AI summary
A database system with methodology for generating bushy nested loop join trees is described. In one embodiment, for example, an improved method is described for optimization of a query requesting data from a database, the method comprises steps of: generating a left deep operator tree for returning data requested by the query based on traversing a left deep operator tree search space; transforming the left deep operator tree into a semantically correct structure for returning data requested by the query; and building a query execution plan for returning data requested by the query based on the semantically correct structure.