Query Compiler Correlated Scalar Subquery Transformation
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing query compilers face inefficiencies in processing correlated scalar subqueries, leading to increased computational workload and memory usage due to the lack of effective methods for transforming these subqueries into quasi-JOIN types and combining Group by Aggregation and JOIN operations without utilizing common elements.
Innovation Solution
A method and query compiler that identifies correlated scalar subqueries, transforms them into quasi-JOIN types such as AGGREGATION INNER/OUTER JOIN and MAX1ROW INNER/OUTER JOIN, and optimizes query execution by reusing computed results, reducing workload and memory usage through strategic grouping and joining operations.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Ease of manufacture
If Group by Aggregation and Aggregation Join are carried out in consecutive order without utilizing common elements, then the query execution follows a straightforward sequential process, but the computational workload of processing units increases exponentially
Solution Approach 1:
The patent combines Group by Aggregation and Aggregation Join operations into a single integrated operation that processes common elements simultaneously. Instead of executing these operations sequentially, the system merges them to process shared data elements in one pass, reducing redundant computations and exponentially decreasing the computational workload on processing units.
2Device complexity
If subqueries are transformed into join type by unnesting without considering correlated scalar subquery types, then the transformation process is simplified, but the method fails to optimize query execution effectively
Solution Approach 1:
The patent applies different transformation strategies based on the specific type of correlated scalar subquery identified. Rather than using a single uniform transformation approach, the system analyzes the local characteristics of each subquery and applies the most appropriate transformation method (such as quasi-JOIN, aggregation join, or max1row join) to that specific case, thereby optimizing execution effectiveness for each subquery type.
3Ease of operation
If computation is performed without reusing common elements between Group by Aggregation and Aggregation Join, then the execution flow is simpler, but the computational amount and memory usage increase
Solution Approach 1:
The patent performs preliminary identification and marking of common elements between Group by Aggregation and Aggregation Join operations. By detecting these common elements in advance and preparing them for reuse before the actual execution, the system enables efficient resource utilization during query processing, reducing both computational amount and memory usage while maintaining a manageable execution flow.
Data Source
AI summary
A query complier analyzes a query to identify a correlated scalar subquery. The query complier transforms the query having the correlated scalar subquery into a query of AGGREGATION INNER/OUTER JOIN or MAX1ROW INNER/OUTER JOIN depending on a result type of the correlated scalar subquery. The AGGREGATION INNER/OUTER JOIN performs JOIN on the rows of the correlated scalar subquery with the rows of a main query and AGGREGATE on the joined rows and returns a result of the joined rows of the main query and aggregation value thereof. The MAX1ROW INNER/OUTER JOIN performs JOIN on the rows of the correlated scalar subquery with the rows of a main query, raises Error when the number of joined rows of the subquery is two or more and returns a result of the row of the main query and the joined row of the subquery.