Cost-Based Join Optimization for Union All Views
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing approaches to processing join queries involving Union All (UA) views lead to poor performance due to high costs of spooling and joining large tables, and are limited by rule-based optimizations that result in suboptimal join plans.
Innovation Solution
A cost-based optimization method that breaks down logical joins with UA views into multiple physical joins, using join indexes and aggregate indexes to optimize join ordering and execution, allowing for more accurate statistics and flexible planning across various database systems.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If all branches of the UA view are spooled in a common spool, then the join query can be executed, but the spooling cost becomes prohibitively expensive and query performance deteriorates
Solution Approach 1:
The patent segments the monolithic spooling operation into multiple smaller spooling operations, one for each branch of the UA view. Instead of spooling all branches into a single common spool, the system creates separate spools for each branch, allowing independent management and optimization of each spooling operation. This segmentation reduces the overall spooling cost by avoiding the creation of one extremely large spool.
Solution Approach 2:
The patent introduces a new dimension to the join execution model by creating multiple spools along the spool dimension rather than consolidating everything into a single spool. This dimensional change allows the system to explore different join permutations and execute joins in optimized sequences, transforming the problem from a single-dimension (one spool) to multi-dimension (multiple spools with different join orders).
2Reliability
If a large spool is created by spooling all branches, then the join operation can proceed, but the cost of joining the large spool with other tables becomes excessively costly
Solution Approach 1:
The patent segments the join operation into multiple smaller join operations. Instead of joining one large spool containing all branches with other tables, the system performs separate join operations for each branch spool. This segmentation allows each join to operate on smaller, more manageable data sets, significantly reducing the computational cost of each join operation.
Solution Approach 2:
The patent performs preliminary join operations between individual branch spools and other tables before final consolidation. By executing joins earlier in the process at the branch level rather than waiting until all branches are spooled into a single large spool, the system reduces the data volume that needs to be processed in subsequent join operations.
3Productivity
If rule-based optimizations are used to push joins into UA views, then specific scenarios can be optimized, but the optimizations are limited to covered scenarios and result in suboptimal join plans
Solution Approach 1:
The patent changes the fundamental parameters of the optimization approach by transitioning from rule-based optimization to cost-based optimization. Instead of applying predefined rules that work only for specific scenarios, the system calculates actual costs for different join permutations and selects the optimal plan based on these cost calculations. This parameter change enables the system to handle a broader range of scenarios adaptively.
Solution Approach 2:
The patent introduces dynamics into the optimization process by evaluating multiple possible join permutations and selecting the best one based on calculated costs. Rather than following static rule-based paths, the system dynamically determines the optimal join order and strategy by comparing different execution plans and their associated costs, allowing it to adapt to various query scenarios and data characteristics.
Data Source
AI summary
A query having a Union All view is identified. A logical join between Union AH view/derived table and other tables is broken down into multiple physical joins. The physical joins are pushed to the branches. Cost-based processing statistics are obtained for the branches. An optimal plan for the joins is selected based on the statistics; representing an optimal query execution for the query. The optimal query execution plan is provided to a database engine for executing the optimal query execution plan against a data warehouse.