SQL Query Optimizer Analytical Function Join Execution
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Optimizing the execution order between analytical functions (AFs) and joins in SQL queries is challenging due to the complexity and cost uncertainty of AFs, which are often treated as black boxes, and the involvement of external engines leading to high data transfer and network overheads.
Innovation Solution
A method that determines AF properties such as 'surplus', 'rowIndependence', and 'partitionIndependence' to infer query-level properties, allowing for re-writing the query to either push or pull join operations within the AF, thereby optimizing the execution order and reducing data transfer and resource utilization.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Adaptability or versatility
If analytical functions are integrated within SQL queries, then query functionality is enhanced, but query optimization becomes difficult due to black box nature and unknown costs
Solution Approach 1:
The patent changes the parameters of analytical functions by defining specific properties (rowIndependence, partitionIndependence, surplus) that transform the black box functions into optimizable operations. This allows the query optimizer to make informed decisions about execution order and join placement based on these defined parameters.
Solution Approach 2:
The patent segments the analytical function execution by identifying independent rows and partitions, allowing the join operation to be pushed into specific independent segments where it can be executed efficiently without affecting other segments.
2Adaptability or versatility
If analytical functions are provided by external engines, then functionality is extended, but data transfer and network overhead increase
Solution Approach 1:
The patent performs preliminary actions by pushing join operations into the analytical function execution plan before data is transferred to external engines. This preprocessing reduces the volume of data that needs to be transferred over the network by filtering and joining data locally first.
Solution Approach 2:
The patent extracts the join operation from the external engine execution context and places it in the database system's execution context, separating the join operation from the analytical function execution to reduce data transfer requirements.
3Loss of energy
If join operations are performed before analytical functions, then data transfer is reduced, but execution order optimization is lost
Solution Approach 1:
The patent makes the execution order dynamic by using the defined analytical function properties to determine the optimal execution order. The system can adaptively decide whether to push joins into AFs or execute them before AFs based on the specific properties of each function, rather than following a fixed execution order.
4Loss of energy
If join operations are pushed into analytical functions, then data transfer is minimized, but query complexity increases
Solution Approach 1:
The patent creates a universal framework that handles multiple scenarios (row-independent AFs, partition-independent AFs, AFs with surplus columns) through a unified approach of defining properties and using them to guide optimization decisions, rather than requiring separate handling for each case.
Data Source
AI summary
Execution of a query invoking an analytical function (AF) is optimized. The query includes a join operation between an AF table and an AuxiliaryTable. A determination is made that the AF includes a plurality of AF properties. Query-level properties about the query are inferred. A determination is made to change an order of the join operation from the plurality of AF properties and query-level properties.