Uncorrelated Subquery Optimization via Row Estimation and Pre-execution
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Conventional uncorrelated subquery optimization methods, such as semi join and materialized optimizations, lead to reduced execution efficiency of database query statements due to repeated access to subquery tables or temporary tables, especially when dealing with large data volumes.
Innovation Solution
An optimization method that determines the presence of an uncorrelated subquery in a database query statement, estimates the number of rows, and executes the subquery only if below a threshold, rewriting the target clause with the execution result to eliminate the subquery, thereby reducing unnecessary access and improving efficiency.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If conventional uncorrelated subquery optimization methods (semi join or materialized optimization) are used, then the subquery can be processed, but the execution efficiency is reduced due to repeated access to subquery tables or temporary tables
Solution Approach 1:
The patent applies preliminary action by executing the uncorrelated subquery in advance during the logic optimization phase before the main query execution. The subquery is executed once to obtain its result set, which is then cached and reused by the main query, eliminating the need for repeated subquery executions and table accesses during the main query processing.
Solution Approach 2:
The patent uses copying by creating a cached copy of the subquery result set that can be reused by the main query. Instead of repeatedly accessing the original subquery table, the system uses the pre-computed and cached result data, reducing I/O operations and improving execution efficiency.
2Productivity
If the subquery is executed repeatedly during main query processing, then the query can be answered, but computing resources are wasted due to repeated access to subquery tables
Solution Approach 1:
The system performs the subquery execution as a preliminary action during the logic optimization phase, before the main query needs to access the subquery table. This pre-computation eliminates repeated table accesses during main query processing, reducing computing resource consumption.
Solution Approach 2:
The patent creates a cached copy of the subquery result set that can be reused by the main query without repeatedly accessing the original subquery table. This copying approach reduces I/O operations and computing resource consumption while maintaining query processing speed.
3Device complexity
If uncorrelated subquery optimization is applied without row estimation, then the optimization process is simple, but it cannot determine whether to execute the subquery based on result set size
Solution Approach 1:
The patent implements feedback by using row estimation to determine whether to execute the subquery optimization path. The system estimates the number of rows in the subquery result set and uses this feedback information to decide whether to proceed with subquery execution and rewriting, allowing adaptive optimization based on data volume characteristics.
Data Source
AI summary
The embodiments of this application provide an uncorrelated subquery optimization method and apparatus, and a storage medium. The method includes determining whether there is an uncorrelated subquery statement in a target clause in a database query statement. In response to the determination that there is the uncorrelated subquery statement in the target clause in the database query statement, the method includes obtaining an estimated number of rows of an execution result set corresponding to the target clause; and determining whether the estimated number of rows is less than a preset threshold. In response to the determination that the estimated number of rows is less than a preset threshold, the method includes executing the uncorrelated subquery statement, and rewriting the target clause according to an execution result set of the uncorrelated subquery statement, to eliminate the uncorrelated subquery statement.