Simulating Bushy Tree Execution Plans for Snowstorm Schema Queries
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Left deep tree execution plans are inefficient for queries against snowstorm schema, which contains many large fact tables and small dimension tables, as they lead to expensive and less efficient joins due to the large size of intermediate results.
Innovation Solution
Simulating bushy tree execution plans by using unmergeable inline views in database systems that only support left deep tree plans, allowing joins between large fact and small dimension tables to be performed first, reducing the size of intermediate results and improving efficiency.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Adaptability or versatility
If left deep tree execution plans are used for snowstorm schema queries, then the execution plan is compatible with standard relational database systems, but the join operations become expensive and inefficient due to large intermediate results
Solution Approach 1:
The patent segments the execution plan into two distinct phases: a bushy tree phase for joining fact tables with dimension tables to produce compact intermediate results, and a left deep tree phase for subsequent joins. This segmentation allows the query to benefit from efficient join ordering in the first phase while maintaining compatibility with standard database execution models in the second phase.
Solution Approach 2:
The patent performs preliminary joins between fact tables and dimension tables using a bushy tree execution plan before the main query execution. This preliminary action produces reduced-size intermediate results that are then used in subsequent left deep tree joins, effectively preparing the data in advance to avoid the inefficiency of joining large tables directly.
2Device complexity
If left deep tree execution plans are used, then the execution plan structure is simple and standard, but the size of intermediate results becomes unmanageably large
Solution Approach 1:
The execution plan is segmented into distinct phases with different structures. The initial phase uses a bushy tree structure optimized for reducing result sets by joining fact tables with small dimension tables, while subsequent phases use left deep tree structures. This segmentation allows each phase to be optimized for its specific purpose while managing intermediate result sizes.
Solution Approach 2:
The patent performs preliminary filtering and joining operations using bushy tree execution before the main query processing. This preliminary action reduces the quantity of data that needs to be processed in subsequent stages, preventing intermediate results from becoming unmanageably large while maintaining a relatively simple overall execution plan structure.
3Ease of manufacture
If joins are performed in serial order using left deep tree, then the execution plan is easy to generate, but the cost of joins increases significantly for snowstorm schema
Solution Approach 1:
The patent implements a dynamic execution plan that adapts to the specific characteristics of snowstorm schema. The system automatically detects snowstorm schema patterns and switches from a static left deep tree execution plan to a dynamic hybrid plan that uses bushy tree execution for initial joins. This dynamic approach optimizes join operation cost while maintaining reasonable ease of generation through automated detection and transformation.
Solution Approach 2:
The patent changes the execution parameter from a fixed left deep tree structure to a hybrid structure with bushy tree phases. This parameter change allows the system to optimize join operation cost by performing joins in an order that minimizes intermediate result sizes, while the automated detection and transformation mechanisms keep the generation process manageable.
Data Source
AI summary
Methods for transforming a query to simulate a bushy tree execution plan for queries containing joins in series are provided. Left deep tree execution plans are supported by most relational database systems but are inefficient at processing queries directed to databases with snowstorm schema. A snowstorm schema contains several large fact tables and many smaller dimension tables, which make reference to one another. Bushy tree execution plans can be much more efficient for processing queries to snowstorm schema. The decision to choose between left-deep and bushy tree execution plans are based on the relative costs of the two execution plans. The methods provided transform queries which are otherwise executed with left deep tree execution plans into queries which are executed with simulated bushy tree execution plans.